Christian, It looks like you should probably write a Transformer for your DIH script. I assume you have a child entity set up for "PriceTable". Add a Transformer to this entity that will look at the value of "currency" and "price", remove these from the row, then add them back in with "currency" as the field name and "price" as the column value.
By the way, it would likely be better if instead of field names like "EUR" and "CHF", you created a dynamic field entry in schema.xml with a dynamic field like this: <dynamicField name="CURRENCY_*" type="tfloat" indexed="true" stored="false" /> Then have your DIH Transformer prepend "CURRENCY_" in front of the field name. This way should your company ever add a new currency, you wouldn't need to change your schema. For more information on writing a DIH Transformer, see http://wiki.apache.org/solr/DIHCustomTransformer If you would rather use a scripting language such as javascript instead of writing your Transformer in java, see http://wiki.apache.org/solr/DataImportHandler#ScriptTransformer . James Dyer E-Commerce Systems Ingram Content Group (615) 213-4311 -----Original Message----- From: Christian Bordis [mailto:c.bor...@epages.com] Sent: Tuesday, August 09, 2011 5:22 AM To: 'solr-user@lucene.apache.org' Subject: Problem with DIH: How to map key value pair stored in 1-N relation from a JDBC Source? Hi! After 1,5 days digging on google, solr wiki, solr 1.4 book (Smiley/Pugh), solr-user mailing list no solution turn up for my problem *sigh*. I use: - solr 3.3 - Date Import Handler 3.3 - JDBC source is MySQL Constrains: - No changes to core database schema - I can only add new views, stored procedures/functions My Problem: Our users can set prices for their product as they wish, regardless of any currency conversion rate etc. These prices are stored in an extra table, a classic 1-N relation. ProductTable ProductID Name ... 1 Fairydust 2 Pot of Gold (rainbow not included) ... PriceTable ID ProductID Currency Price ... 1 1 EUR 3,99 2 1 CHF 2,22 3 1 USD 4,50 4 2 EUR 9999 5 2 GBP 3599 ... My indexed documents should contain following fields(columns), hence I want to do facets over currencies. Fairydust: ProductID Name EUR CHF USD Pot of Gold: ProductID Name EUR GBP Mutivalued fields wont work, because I need facets and I can't afford 192 columns in MySQL for all currencies supported by our software. Dynamic fields won't do the trick either as far as I know. I already solved this problem with early shoddy lucene prototype. I didn't need think twice. Do I need to switch to SolrJ and alike for this? If I switch to SolrJ do I suffer a panalty in index performance? I think DIH provide the solution for this problem but I can't find it. Any suggestions are welcome. Thanks for reading, Christian Bordis
