Databases
StackOverflow2013
Postsdbo
POcoldfusion fw1 (hibernate) basic join between tables - "Path expected for join!"
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POcoldfusion fw1 (hibernate) basic join between tables - "Path expected for join!"
    text
    copied!<p>I'm trying to do a basic join which would take a few seconds in SQL but... I'm trying to get it working with ORMExecuteQuery (Coldfusion 9 based on Hibernate).</p> <p>I have 3 objects: - Contact - ContactCategory_Link - ContactCategory</p> <p>Components details will follow after the short description of what's working and what's not working.</p> <p>Basically a contact can have many categories, and a category can have many contacts as well. Since I need to add different parameters for every link (for example I'd like to order the categories for each contact (end-users have to be able to reorder the categories), plus other information required by my system). I didn't use the many-to-many relationship because it does not seem to be possible to add that kind of additional information.</p> <p>So here is the request that works perfectly:</p> <pre><code>&lt;cfset response["rows"] = ORMExecuteQuery(" SELECT new map(c.name as Name) FROM Contact c ")&gt; </code></pre> <p>It gives perfectly the contact name. However each time I try to add another table, it will fail. For example:</p> <pre><code>&lt;cfset response["rows"] = ORMExecuteQuery(" SELECT new map(c.name as Name) FROM Contact c JOIN ContactCategory_Link ")&gt; </code></pre> <p>Will give:</p> <pre><code>Path expected for join! </code></pre> <p>Even if I'm changing <code>ContactCategory_Link</code> for <code>ContactCategory_Link.contact</code>, it will give something like:</p> <pre><code>Invalid path: 'null.contact' </code></pre> <p>So I'm guessing my components CFC are not set correctly, but I don't see why.</p> <p>Could you help me on this subject?</p> <hr> <p>Here's the code of each object:</p> <pre><code>&lt;cfcomponent displayname="Contact" entityname="Contact" table="step8_contact" persistent="true" output="false"&gt; &lt;cfproperty name="id" column="contactID" type="guid" fieldtype="id" setter="false" unique="true" notnull="true" generated="insert" generator="identity"&gt; &lt;cfproperty name="name" column="name" type="string" length="125" required="true"&gt; &lt;cfproperty name="categories" fieldtype="one-to-many" singularname="category" fkcolumn="contactID" cfc="ContactCategory_Link" missingRowIgnored="true" cascade="all-delete-orphan"&gt; &lt;/cfcomponent&gt; </code></pre> <hr> <pre><code>&lt;cfcomponent displayname="ContactCategory_Link" entityname="ContactCategory_Link" table="step8_contactcategory_link" persistent="true" output="false"&gt; &lt;cfproperty name="id" column="contactcategory_linkID" type="numeric" fieldtype="id" setter="false" unique="true" notnull="true" generated="insert" generator="identity"&gt; &lt;cfproperty name="orderId" column="orderId" type="numeric" required="true"&gt; &lt;!---notnull="true"---&gt; &lt;cfproperty name="contact" fieldtype="many-to-one" fkcolumn="contactID" cfc="Contact" missingRowIgnored="true"&gt; &lt;cfproperty name="contactcategory" fieldtype="many-to-one" fkcolumn="contactcategoryID" cfc="ContactCategory" missingRowIgnored="true"&gt; &lt;/cfcomponent&gt; </code></pre> <hr> <pre><code>&lt;cfcomponent displayname="ContactCategory" output="false" persistent="true" entityname="ContactCategory" table="step8_contactcategories" hint="" cacheuse="read-only" cachename="contactcategory"&gt; &lt;cfproperty name="id" column="contactcategoryID" type="numeric" fieldtype="id" setter="false" unique="true" notnull="true" generated="insert" generator="identity"&gt; &lt;cfproperty name="label" column="label" type="string" length="255" required="true" notnull="true"&gt; &lt;cfproperty name="orderId" column="orderId" type="numeric" required="true" notnull="true"&gt; &lt;cfproperty name="categories" fieldtype="one-to-many" singularname="category" fkcolumn="contactcategoryID" cfc="ContactCategory_Link" missingRowIgnored="true" cascade="all-delete-orphan" lazy="true"&gt; &lt;/cfcomponent&gt; </code></pre> <p>Thanks for your help.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload