Mapping one-to-many entities in SOLR

Question

I'm trying to map a few entities from an existing database to SOLR.

The tables are:

Hotel: hotel_id hotel_name

HotelToCategory: hotel_id category_id rate

Category: category_id name value

How can I use DataImportHandler to produce documents like this:

{
    hotel_name: 'name',
    hotel_id: 1,
    categories: [
       { category_name: 'cname',
         value: 'val',
         rate: 3,
       }
    ]
}

Any help will be greatly appreciated!

Answer 1

Relationships are indexed using stacked entities in DIH. Have a look in the DIH page in the Solr wiki.

There's also a few basic examples of this included in Solr distributions, have a look in examples/example-DIH.

There is a limitation here though, solr does not (currently) support relationships between index documents, so you will have to find a workaround for indexing this. For instance by just storing display data in a non-indexed field (which might require very frequent reindexing):

<document>
    <entity name="hotel" query="select * from hotel">
        <field column="id" name="hotel_id" />
        <field column="hotel_name" name="hotel_name" />
        <entity name="hotel_category_display"
                query="SELECT STATEMENT THAT RETURNS JSON REPRESENTATION">
            <field column="category" name="category" />
        </entity>
</document>

Or by storing just category ID and do lookups (either against the database, or index categories separately and lookup against Solr) at search time:

<entity name="hotel_category_display"
        query="SELECT STATEMENT THAT RETURNS JSON REPRESENTATION">
    <field column="category" name="category" />
</entity>