OK, I think I found some information, supposedly TemplateTransformer will return an empty string if the value of a variable is null. Some people say to use the regex transformer instead, can anyone clarify this? Thanks
-----Original Message----- From: Greg Georges [mailto:greg.geor...@biztree.com] Sent: 15 février 2011 13:38 To: solr-user@lucene.apache.org Subject: Question regarding inner entity in dataimporthandler Hello all, I have searched the forums for the question I am about to ask, never found any concrete results. This is my case. I am defining the data config file with the document and entity tags. I define with success a basic entity mapped to my mysql database, and I then add some inner entities. The problem I have is with the one-to-one relationship I have between my "document" entity and its "documentcategory" entity. In my document table, the documentcategory foreign key is optional. Here is my mapping <document> <entity name="document" query="select DocumentID, DocumentID as documentId, CreationDate as creationDate, DocumentName as documentName, Description as description, DescriptionAbstract as descriptionAbstract, Downloads as downloads, Downloads30days as downloads30days, Downloads90days as downloads90days, PageViews as pageViews, PageViews30days as PageViews30days, PageViews90days as pageViews90days, Bookmarks as bookmarks, Bookmarks30days as bookmarks30days, Bookmarks90days as bookmarks90days, DocumentRating as documentRating, DocumentRating30days as documentRating30days, DocumentRating90days as documentRating90days, LicenseType as licenseType, BizTreeLibraryDoc as bizTreeLibraryDoc, DocFormat as docFormat, Price as price, CreatedByMemberID as memberId, DocumentCategoryID as categoryId, IsFreeDoc as isFreeDoc from document" transformer="TemplateTransformer"> <field column="id" name="id" template="doc${document.documentId}" /> <field column="documentId" name="docId"/> <field column="creationDate" name="creationDate" /> <field column="documentName" name="documentName" /> <field column="description" name="description" /> <field column="descriptionAbstract" name="descriptionAbstract" /> <field column="downloads" name="downloads" /> <field column="downloads30days" name="downloads30days" /> <field column="downloads90days" name="downloads90days" /> <field column="pageViews" name="pageViews" /> <field column="pageViews30days" name="pageViews30days" /> <field column="pageViews90days" name="pageViews90days" /> <field column="bookmarks" name="bookmarks" /> <field column="bookmarks30days" name="bookmarks30days" /> <field column="bookmarks90days" name="bookmarks90days" /> <field column="documentRating" name="documentRating" /> <field column="documentRating30days" name="documentRating30days" /> <field column="documentRating90days" name="documentRating90days" /> <field column="licenseType" name="licenseType" /> <field column="bizTreeLibraryDoc" name="bizTreeLibraryDoc" /> <field column="docFormat" name="docFormat" /> <field column="price" name="price" /> <field column="isFreeDoc" name="isFreeDoc" /> <entity name="category" query="select CategoryID as id, CategoryName as categoryName, MetaTitle as categoryMetaTitle, MetaDescription as categoryMetaDescription, MetaKeywords as categoryMetakeywords from documentcategory where CategoryID = ${document.categoryId}" onError="skip"> <field column="categoryName" name="categoryName"/> <field column="categoryMetaTitle" name="categoryMetaTitle"/> <field column="categoryMetaDescription" name="categoryMetaDescription"/> <field column="categoryMetakeywords" name="categoryMetakeywords"/> </entity> </entity> </document> My first document entity in the database does not have a documentcateogry. When I run the dataimported I get this error message Unable to execute query: select CategoryID as id, CategoryName as categoryName, MetaTitle as categoryMetaTitle, MetaDescription as categoryMetaDescription, MetaKeywords as categoryMetakeywords from documentcategory where CategoryID = Processing Document # 1 Caused by: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 It seems that since the document.categoryId is null it uses an empty string. We would say that the importer does not work like a left join thus returning results even if one child is null. Anyone know a possible solution? Maybe instead of using inner entities, can I define a left join directly in my document query? Thanks BTW: I already tested the config with another child element and everything works fine. Only the case with documentcategory which is sometimes null which causes problems Greg
