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
