title: Sorting on Column in Type Table with ColdFusion ORM
body: <p>I have three tables, with the following structure:</p>
  
  <p><a href="http://dl.dropbox.com/u/2586403/ORMIssues/TableLayout.png" rel="nofollow">http://dl.dropbox.com/u/2586403/ORMIssues/TableLayout.png</a> </p>
  
  <p>The three objects I'm dealing with are here:</p>
  
  <p><a href="http://dl.dropbox.com/u/2586403/ORMIssues/Objects.zip" rel="nofollow">http://dl.dropbox.com/u/2586403/ORMIssues/Objects.zip</a> </p>
  
  <p>I need to be able to get a PartObject, and then pull all of its Attributes, sorted by the AttributeName in the Types table. Here are the problems I'm running into: </p>
  
  <ol>
  <li><p>I can't sort the Attributes property in PartObject by its 
  Attribute.AttributeName property </p></li>
  <li><p>I can't add the Attribute.AttributeName property to the ObjectAttribute entity because I get an error regarding column names. Hibernate is putting the ID on the wrong side of the join</p></li>
  </ol>
  
  <p>Here's the hibernate log file showing the bad query</p>
  
  <pre><code>10/14 16:36:39 [jrpp-12] HIBERNATE DEBUG - select objectattr0_.ID as ID1116_, objectattr0_.AttributeValue as Attribut2_1116_, objectattr0_.AttributeID as Attribut3_1116_, objectattr0_1_.AttributeName as Attribut2_1117_ from ObjectAttributes objectattr0_ inner join Attributes objectattr0_1_ on objectattr0_.ID=objectattr0_1_.AttributeID 
  10/14 16:36:39 [jrpp-12] HIBERNATE ERROR - [Macromedia] [SQLServer JDBC Driver][SQLServer]Invalid column name 'AttributeID'. 
  10/14 16:36:39 [jrpp-12] HIBERNATE ERROR - [Macromedia] [SQLServer JDBC Driver][SQLServer]Statement(s) could not be prepared. 
  </code></pre>
  
  <p>Here's the offending section of the query: </p>
  
  <pre><code>from ObjectAttributes objectattr0_ 
  inner join Attributes objectattr0_1_ on objectattr0_.ID=objectattr0_1_.AttributeID 
  </code></pre>
  
  <p>It should be: </p>
  
  <pre><code>from ObjectAttributes objectattr0_ 
  inner join Attributes objectattr0_1_ on objectattr0_.AttributeID=objectattr0_1_.ID 
  </code></pre>
  
  <p>The AttributeName property on the ObjectAttribute.cfc is the one causing the problem: </p>
  
  <pre><code>component  output="false" persistent="true" table="ObjectAttributes" 
  { 
          property name="ID" column="ID" generator="native" type="numeric" ormtype="int" fieldtype="id" unsavedvalue="0" ; 
          property name="AttributeValue" type="string" ; 
          property name="Attribute" fieldtype="many-to-one" cfc="Attribute" fkcolumn="AttributeID" fetch="join"; 
          property name="AttributeName" table="Attributes" joincolumn="AttributeID" ; 
  } 
  </code></pre>
  
  <p>I've also tried using a formula to get the AttributeName on the ObjectAttribute entity, like so:</p>
  
  <pre><code>component  output="false" persistent="true" table="ObjectAttributes"
  {
      property name="ID" column="ID" generator="native" type="numeric" ormtype="int" fieldtype="id" unsavedvalue="0" ;
      property name="AttributeValue" type="string" ;
      property name="Attribute" fieldtype="many-to-one" cfc="Attribute" fkcolumn="AttributeID" fetch="join";
      property name="AttributeName" type="string" formula="(SELECT A.AttributeName FROM Attributes A WHERE A.ID = AttributeID)";
  }
  </code></pre>
  
  <p>This works, but I can't sort by that computed column. If I then adjust PartObject.cfc like so:</p>
  
  <pre><code>property name="Attributes" cfc="ObjectAttribute" type="array" fkcolumn="ObjectID" fieldtype="one-to-many" orderby="AttributeName";
  </code></pre>
  
  <p>I get the following errors in the hibernatesql log:</p>
  
  <pre><code>10/17 16:51:55 [jrpp-0] HIBERNATE DEBUG - select attributes0_.ObjectID as ObjectID2_, attributes0_.ID as ID2_, attributes0_.ID as ID244_1_, attributes0_.AttributeValue as Attribut2_244_1_, attributes0_.AttributeID as Attribut3_244_1_, ((SELECT A.AttributeName FROM Attributes A WHERE A.ID = attributes0_.AttributeID)) as formula25_1_, attribute1_.ID as ID246_0_, attribute1_.AttributeName as Attribut2_246_0_ from ObjectAttributes attributes0_ left outer join Attributes attribute1_ on attributes0_.AttributeID=attribute1_.ID where attributes0_.ObjectID=? order by attributes0_.AttributeName
  10/17 16:51:55 [jrpp-0] HIBERNATE ERROR - [Macromedia][SQLServer JDBC Driver][SQLServer]Invalid column name 'AttributeName'.
  10/17 16:51:55 [jrpp-0] HIBERNATE ERROR - [Macromedia][SQLServer JDBC Driver][SQLServer]Statement(s) could not be prepared.
  </code></pre>
  
  <p>Here's a dump <em>without</em> that property to show that the rest of the relationships are working properly: </p>
  
  <p><a href="http://dl.dropbox.com/u/2586403/ORMIssues/Dump.pdf" rel="nofollow">http://dl.dropbox.com/u/2586403/ORMIssues/Dump.pdf</a> </p>
  
  <p>I have no idea how to fix this issue. Any help you can provide would be greatly appreciated. </p>
  
  <p>Thanks, </p>
  
  <p>Dan</p>