Greg, a few things, i noticed while reading your post:
1) you don't need an <field>-assignment for fields where the name does not change, you can just skip that. <field column="creationDate" name="creationDate" /> - just to name one example 2) TemplateTransformer (http://wiki.apache.org/solr/DataImportHandler#TemplateTransformer) has no name-attribute, just column and template 3) again TemplateTransformer - never tried it out, but it should return 'doc' in your case, when ${document.documentId} has no value .. it should not work like MySQL's CONCAT() which returns null, if at least one argument is null. but actually i see no reason for using RegexTransformer?! 4) Your Sub-Entity-Problem is more or less obviously ;) If ${document.categoryId} is empty (regardless an empty string or just null) your Query is invalid. What will work, wrap the var with Quotes (select field1, field2 from table where field3 = '$variable') then it will work .. w/ or w/o an value Hope that Helps, Stefan On Tue, Feb 15, 2011 at 8:13 PM, Greg Georges <greg.geor...@biztree.com> wrote: > 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 > >
