StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSorting on Column in Type Table with ColdFusion ORM
text
Body
copied!<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>

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