title: coldfusion fw1 (hibernate) basic join between tables - "Path expected for join!"
body: <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>