Mapping elements with multiple rows from mysql to solr

Question

So, I have a normalized table with some data that I want to add to the Solr index, something similar to this

+----+--------------+--------------+---------+ | id | name | attribute | value | +----+--------------+--------------+---------+ | 1 | Apple | color | green | | 1 | Apple | shape | round | | 1 | Apple | origin | Belgium | | 2 | Motorbike | type | fast | | 2 | Motorbike | nr of wheels | 2 | | 3 | Office chair | color | grayish | | 3 | Office chair | spins | yes | +----+--------------+--------------+---------+ 

Now I would prefer it to be indexed as a single document by a unique identifier (i.e. element). But then I have to combine n attributes into one document. To do this, I will need to do the magic with my dataConfig. But how can I store and display n fields? Is the right time to use dynamic fields?

Here is my current attempt. I am sure this is not true.

 <dataConfig> <dataSource type="JdbcDataSource" driver="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost/mystuff" user="root" password="*****"/> <document name="doc"> <entity name="t1" query="select * from item_to_id_table"> <field name="id" column="id"/> <field name="name" column="name"/> <entity name="t2" query="select * from my_flat_table" cacheKey="t1.id" cacheLookup="t2.id"> <!-- alt 1 --> <field name="$(t2.attribute)" column="value" /> <!-- alt 2 --> <entity name="properties" query="select property, value from t2" cacheKey="$(t2.attribute)" cacheLookup="property"> <field name="$(properties.property)" column="value" /> </entity> </entity> </entity> </document> </dataConfig> 

I am sure that none of the two alternatives will be valid, I will try them in the near future if I can not understand something better. Possibly converting a script as a third alternative.

Is this use case reasonable for use with Solr?

Answer 1

I solved this method described here .

In short, I used a script transformation to turn property entity strings into fields with the p_ prefix. Most likely this (example code, there may be errors):

 <dataConfig> <dataSource type="JdbcDataSource" driver="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost/mystuff" user="root" password="*****"/> <script> <![CDATA[ function formProperty(row) { var propName = row.get("property"); var propVal = row.get("value"); var fieldName = "p_" + propName; row.put(fieldName,propVal); return row; } ]]> </script> <document name="doc"> <entity name="t1" query="select * from item_to_id_table"> <field name="id" column="id"/> <field name="name" column="name"/> <entity name="t2" query="select * from my_flat_table where my_flat_table.id = ${t1.id}" transformer="script:formProperty"> </entity> </entity> </document> </dataConfig> 

Then I mapped them to solr schemas in schema.xml as dynamic fields

 <dynamicField name="p_*" indexed="true" stored="true" type="string"/>