Search code examples
solrdataimporthandler

Dynamic TableName SOLR data import handler

I'm looking to configure SOLR to query a table based on certain data. I unfortunately have to work with how the Database is setup, but here's what I'm after.

I have a table named Company that will contain a certain "prefix" value. I want to use that prefix value to determine what tables I should query for the DIH.

As a quick sample:

<entity name="company" query="Select top 1 prefix from Company">
<field name="prefix" column="prefix"/>
<entity name="item" query="select * from ${company.prefix}item">
<field column="ItemID" name="id"/>
<field column="Description" name="description/>
</entity>
</entity>

However I only ever seem to get 1 document processed despite that table containing over 200,000 rows.

what am I doing wrong?

Solution

  • I think you could achieve this by:

    1. using an stored procedure. You can call a sp from DIH as seen here
    2. inside the stored procedure, you can do the table lookup as needed, and then return the results from the real query.

    Depending on how good you are with MSSql-s SQL, you might be able to just put everything into a single SQL query and use that directly in DIH, but not sure about that.