indexing mutilple tables in solr using DIH

Question

I'm developing search engine using Solr and I've been successful in indexing data from one table using DIH (Dataimport Handler). What I need is to get search result from 5 different tables. I couldn't do this without help.

if we assume x table with x rows, there should be x * x documents from each table, which lead to, 5x documents if I have 5 tables as total. In dataconfig.xml, I created 5 seperate entities in single document as shown below. the result from indexed data when I query *:* is only 6 of the entity users and 3 from entity classes which is the number of users total rows which is 9.

Clearly, this way didn't work for me, so how can I achieve this using only single core?

note: I followed DIHQuickStart and DIH tutorial which didn't help me.

<document>
    <!-- Users -->
    <entity dataSource="JdbcDataSource" name=" >
            <field column="name" name="name" sourceColName="name" />
            <field column="username" name="username" sourceColName="username"/>
            <field column="email" name="email" sourceColName="email" />
            <field column="country" name="country" sourceColName="country" />
    </entity>

<!-- Classes -->
    <entity dataSource="JdbcDataSource" name="classes" >
            <field column="code" name="code" sourceColName="code" />
            <field column="title" name="title" sourceColName="title" />
            <field column="description" name="description" sourceColName="description" />
    </entity>

    <!-- Schools -->
    <entity dataSource="JdbcDataSource" name="schools" >
            <field column="school_name" name="school_name" sourceColName="school_name" />
            <field column="country" name="country" sourceColName="country" />
            <field column="city" name="city" sourceColName="city" />
    </entity>

    <!-- Resources -->
    <entity dataSource="JdbcDataSource" name="resources" >
            <field column="title" name="title" sourceColName="title" />
            <field column="description" name="description" sourceColName="description" />
    </entity>

    <!-- Tasks -->
    <entity dataSource="JdbcDataSource" name="tasks"  >
            <field column="title" name="title" sourceColName="title" />
            <field column="description" name="description" sourceColName="description" />
    </entity>

</document>

Joyce Joyce · Accepted Answer · 2011-06-15T13:11:46

you need to look at the structures of your tables then either create queries with joins or creat nested entities like this for example

<dataConfig>
<dataSource driver="org.hsqldb.jdbcDriver" url="jdbc:hsqldb:/temp/example/ex" user="sa" />
    <document name="schools">
        <entity name="school" query="select * from schools s ">
            <field column="school_name" name="school_Name" />       
        <entity name="school_class" query="select * from schools_classes sc where sc.school_name = '${school.school_name}'">
        <field column="class_code" name="class_code" />     
        <entity name="class" query="select class_name from classes c where c.class_name= '${school_class.class_code}'">
           <field column="class_name" name="class_name" />      
        </entity>
        </entity>
        </entity>
    </document>
</dataConfig>

indexing mutilple tables in solr using DIH

1 Answers