CF9 HQL Statement for many-to-many and Multiple Criteria

Posted by Jeremy Battle on Stack Overflow See other posts from Stack Overflow or by Jeremy Battle
Published on 2010-03-26T03:40:10Z Indexed on 2010/03/26 3:43 UTC
Read the original article Hit count: 532

Filed under:

hibernate

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?

Developer IT