用于多对多和多个条件的 CF9 HQL 语句
我有以下设置:
Listing.cfc
component persistent="true"
{
property name="ListingId" column="ListingId" type="numeric" ormtype="int" fieldtype="id" generator="identity";
...
property name="Features" type="array" hint="Array of features" singularname="Feature" fieldtype="many-to-many" cfc="Feature" linktable="Listing_Feature" FKColumn="ListingId" inversejoincolumn="FeatureId";
}
Feature.cfc
component persistent="true" table="Feature" schema="dbo" output="false"
{
property name="FeatureId" column="FeatureId" type="numeric" ormtype="int" fieldtype="id" generator="identity";
property name="FeatureDescription" column="FeatureDescription" type="string" ormtype="string";
...
/*property name="Listings" fieldtype="many-to-many" cfc="Listing" linktable="Listing_Feature" fkcolumn="FeatureId" inversejoincolumn="ListingId" lazy="true" cascade="all" orderby="GroupOrder";*/
}
我可以使用以下方法选择具有特定功能的所有列表:
<cfset matchingListings = ormExecuteQuery("from Listing l left join l.Features as feature where feature.FeatureId = :feature",{feature = 110}) />
这很好,但是,我希望能够选择具有多个功能的所有列表(例如列表既有“洗碗机”又有“车库”)
经过几个小时的谷歌搜索和查看休眠文档后未能找到不会给我错误的解决方案。我的猜测是,解决方案非常简单,我只是想太多了......有人有什么建议吗?
I have the following setup:
Listing.cfc
component persistent="true"
{
property name="ListingId" column="ListingId" type="numeric" ormtype="int" fieldtype="id" generator="identity";
...
property name="Features" type="array" hint="Array of features" singularname="Feature" fieldtype="many-to-many" cfc="Feature" linktable="Listing_Feature" FKColumn="ListingId" inversejoincolumn="FeatureId";
}
Feature.cfc
component persistent="true" table="Feature" schema="dbo" output="false"
{
property name="FeatureId" column="FeatureId" type="numeric" ormtype="int" fieldtype="id" generator="identity";
property name="FeatureDescription" column="FeatureDescription" type="string" ormtype="string";
...
/*property name="Listings" fieldtype="many-to-many" cfc="Listing" linktable="Listing_Feature" fkcolumn="FeatureId" inversejoincolumn="ListingId" lazy="true" cascade="all" orderby="GroupOrder";*/
}
I can select all listings that have a particular feature using:
<cfset matchingListings = ormExecuteQuery("from Listing l left join l.Features as feature where feature.FeatureId = :feature",{feature = 110}) />
Which is fine, however, I'd like to be able to select all listings that have multiple features (for example a listing that has both "Dishwasher" AND "Garage")
After a couple hours of googling and looking through hibernate documentation haven't been able to find a solution that won't give me an error. My guess is that the solution is pretty simple and I am just over-thinking it...anyone have any suggestions?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
我不认为这是最有效的方法,但是,它确实产生了我想要的结果
这只会给我提供具有我正在寻找的所有功能的列表,但是,它做了很多加入和查找在 hibernate SQL 日志正在生成:
似乎必须有一种更有效的方法在 HQL 中执行此操作
cf-orm-dev 邮件列表上的 Jon Messer 给了我我认为发布此问题的最正确的解决方案这里为大家提供:
“据我所知,ORMExecuteQuery 不处理列表参数,所以如果你想参数化它们并返回对象,你必须做类似的事情
”
谢谢乔恩!
I don't believe this to be the most efficient way to do this, however, it does produce the result I want
This will give me only listings that have all the features I am looking for but, it does a LOT of joining and looking at the hibernate SQL log is producing:
It just seems like there must be a more efficient way to do this in HQL
Jon Messer on the cf-orm-dev mailing list gave me what I believe is the most correct solution to this question posting it here for everyone:
"As far as I know ORMExecuteQuery doesn't handle list parameters, so if you wanted to param them and return objects you'd have to do something like
"
Thanks Jon!
这应该可行:
如果您想使用绑定参数,您将必须做一些额外的工作,因为 hibernate 这次不喜欢 ColdFusion 列表和/或数组作为绑定参数。您可以在此处找到一些额外信息
This should work:
If you want to go with bound parameters you will have to do some extra work, because hibernate doesn't like ColdFusion Lists and/or Arrays as bound parameters this time. You can find some extra info here