使用ColdFusion ORM在类型表中的列上排序 [英] Sorting on Column in Type Table with ColdFusion ORM
问题描述
我有三个表格，其结构如下：
I have three tables, with the following structure:
http://dl.dropbox.com/u/2586403/ORMIssues/TableLayout.png
这三个对象我正在处理的是这里：
The three objects I'm dealing with are here:
http://dl.dropbox.com/u/2586403/ORMIssues/Objects.zip
我需要能够获得PartObject，然后拉它的所有属性，按照Types表中的AttributeName排序。这是我遇到的问题：
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:
-
我无法通过其
Attribute.AttributeName属性
I can't sort the Attributes property in PartObject by its Attribute.AttributeName property
我无法将Attribute.AttributeName属性添加到ObjectAttribute实体，因为我收到有关列名称的错误。 Hibernate将ID放在连接的错误一侧
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
这里是hibernate日志文件， p>
Here's the hibernate log file showing the bad query
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.
这是查询的令人讨厌的部分：
Here's the offending section of the query:
from ObjectAttributes objectattr0_
inner join Attributes objectattr0_1_ on objectattr0_.ID=objectattr0_1_.AttributeID
它应该是：
from ObjectAttributes objectattr0_
inner join Attributes objectattr0_1_ on objectattr0_.AttributeID=objectattr0_1_.ID
ObjectAttribute.cfc上的AttributeName属性是导致问题的属性：
The AttributeName property on the ObjectAttribute.cfc is the one causing the problem:
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" ;
}
我也试过使用公式来获取ObjectAttribute实体的AttributeName ，像这样：
I've also tried using a formula to get the AttributeName on the ObjectAttribute entity, like so:
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)";
}
这样工作，但是我不能按计算列排序。如果我然后调整PartObject.cfc像这样：
This works, but I can't sort by that computed column. If I then adjust PartObject.cfc like so:
property name="Attributes" cfc="ObjectAttribute" type="array" fkcolumn="ObjectID" fieldtype="one-to-many" orderby="AttributeName";
我在hibernatesql日志中遇到以下错误：
I get the following errors in the hibernatesql log:
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.
这里是转储没有该属性来显示其余的关系正常工作：
Here's a dump without that property to show that the rest of the relationships are working properly:
http://dl.dropbox.com/u/2586403/ORMIssues/Dump.pdf
我不知道如何解决这个问题。任何帮助，你可以提供将非常感谢。
I have no idea how to fix this issue. Any help you can provide would be greatly appreciated.
感谢，
Dan
推荐答案
我在Sam的帮助下解决了这个问题，在我的服务中设置一个方法，按照我想要的顺序返回项目。我只是使用ORMExecuteQuery来获得正确的顺序，如果没有项目，只是返回一个空数组。
I solved this one with Sam's help, by setting up a method in my Service that returned the items in the order I wanted. I just used ORMExecuteQuery to get the items in the right order, and if there were no items, just returned an empty array.
最后的方法看起来像这样，其中规格是按照我想要的顺序直接在对象中设置的：
The final method looked like this, where specifications are set directly in the object in the order I want them:
/**
@hint Gets a SpecGroups object based on ID. Pass 0 to retrieve a new empty SpecGroups object
@ID the numeric ID of the SpecGroups to return
@roles Admin, User
*/
remote ORM.SpecGroups function getSpecGroup(required numeric ID){
if(Arguments.ID EQ 0){
return New ORM.SpecGroups();
}else{
LOCAL.SpecGroup = EntityLoadByPK("SpecGroups", Arguments.ID);
LOCAL.SpecsInGroup = ORMExecuteQuery("SELECT Spec FROM SpecInGroup G WHERE G.SpecGroupID = :GroupID ORDER BY SpecLabel, SpecName", {GroupID = LOCAL.SpecGroup.getID()});
LOCAL.SpecGroup.setSpecifications(LOCAL.SpecsInGroup);
return LOCAL.SpecGroup;
}
}
这篇关于使用ColdFusion ORM在类型表中的列上排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
