First remove the "where" condition from the child entities, then use the "cacheKey" and "cacheLookup" parameters to instruct DIH how to do the join.
Example: <entity name="Cat1" cacheKey="SKU" cacheLookup="Product.SKU" query="SELECT CategoryName from CAT_TABLE where CategoryLevel=1" /> See http://wiki.apache.org/solr/DataImportHandler#CachedSqlEntityProcessor , particularly the 3rd configuration option. James Dyer Ingram Content Group (615) 213-4311 -----Original Message----- From: O. Olson [mailto:olson_...@yahoo.it] Sent: Tuesday, May 21, 2013 9:21 AM To: solr-user@lucene.apache.org Subject: How do I use CachedSqlEntityProcessor? I am using the DataImportHandler to Query a SQL Server and populate Solr with data that has hierarchical relationships. The following is an outline of my table structure: PROD_TABLE -> SKU (Primary Key) -> Title (varchar) -> Descr (varchar) CAT_TABLE -> SKU (Foreign Key) -> CategoryLevel (int i.e. 1, 2, 3 …) -> CategoryName (varchar) I specify the SQL Query in the db-data-config.xml file – a snippet of which looks like: <dataConfig> <dataSource driver="com.microsoft.sqlserver.jdbc.SQLServerDriver" url="jdbc:sqlserver://localhost\...."/> <document> <entity name="Product" query="SELECT SKU, Title, Descr FROM PROD_TABLE"> <field column="SKU" name="SKU" /> <field column="Title" name="Title" /> <field column="Descr" name="Descr" /> <entity name="Cat1" query="SELECT CategoryName from CAT_TABLE where SKU='${Product.SKU}' AND CategoryLevel=1"> <field column="CategoryName" name="Category1" /> </entity> <entity name="Cat2" query="SELECT CategoryName from CAT_TABLE where SKU='${Product.SKU}' AND CategoryLevel=2"> <field column="CategoryName" name="Category2" /> </entity> <entity name="Cat3" query="SELECT CategoryName from CAT_TABLE where SKU='${Product.SKU}' AND CategoryLevel=3"> <field column="CategoryName" name="Category3" /> </entity> </entity> </document> </dataConfig> Unfortunately this is a bit slow, and it was recommended to me to use the CachedSqlEntityProcessor (http://wiki.apache.org/solr/DataImportHandler#CachedSqlEntityProcessor). Hence I modified my db-data-config.xml to look like: <dataConfig> <dataSource driver="com.microsoft.sqlserver.jdbc.SQLServerDriver" url="jdbc:sqlserver://localhost\...."/> <document> <entity name="Product" query="SELECT SKU, Title, Descr FROM PROD_TABLE"> <field column="SKU" name="SKU" /> <field column="Title" name="Title" /> <field column="Descr" name="Descr" /> <entity name="Cat1" query="SELECT CategoryName from CAT_TABLE where SKU='${Product.SKU}' AND CategoryLevel=1" processor="CachedSqlEntityProcessor"> <field column="CategoryName" name="Category1" /> </entity> <entity name="Cat2" query="SELECT CategoryName from CAT_TABLE where SKU='${Product.SKU}' AND CategoryLevel=2" processor="CachedSqlEntityProcessor"> <field column="CategoryName" name="Category2" /> </entity> <entity name="Cat3" query="SELECT CategoryName from CAT_TABLE where SKU='${Product.SKU}' AND CategoryLevel=3" processor="CachedSqlEntityProcessor"> <field column="CategoryName" name="Category3" /> </entity> </entity> </document> </dataConfig> The import works really quickly, but there are no Categories e.g. Category1, Category2 etc. in the imported documents. Any clue’s on how to debug this problem? I should mention that I don’t change my schema.xml or any other file in the config. All I do is switch between the first db-data-config.xml – where I get the Categories as part of the document, and the second, where I do not. I went back and re-verified this result. Thank you all for your help. O. O. -- View this message in context: http://lucene.472066.n3.nabble.com/How-do-I-use-CachedSqlEntityProcessor-tp4064919.html Sent from the Solr - User mailing list archive at Nabble.com.
