Coldfusion Hibernate - Filter results by field value inside collection

Communities ColdBox HMVC
1

Hello, all.

I’ve run into an issue with a Coldfusion app that I’ve been forced to maintain. I created a StackOverflow question here: http://stackoverflow.com/q/20023824/677526

Basically, I’m attempting to filter a query by a child collection; I’ve tried to do this using SubCriteria, but I’m running into issues where Coldfusion isn’t finding the correct method (giving me errors saying “method not found”). This is happening specifically for the .in() method defined by the Coldbox ORM docs.

Has anyone run into this issue before? It’s holding me back and I’m not sure how to work around it.

This is the code block I’m working with. Please let me know if I should post the full file.

if (NOT showEventsWithoutQuantifications){
    EventCriteria.isNotEmpty('quantifications');

    var eventStatus = eventStatusService.findWhere(entityName="EventStatus", criteria={eventStatusOrder=javaCast( "int", 150 )}); //approved

    // According to the Coldbox documentation, this should have worked. It doesn't.
    // var QuantificationService = quantificationService.newCriteria()
    //      .isEq("Quantification.status", eventStatus)
    //      .withProjections(property="event.eventID");
    // EventCriteria.in("eventID", QuantificationService); // .in() method not found. Why?
    // EventCriteria.add(EventCriteria.restrictions.in("eventID", QuantificationService)); // .in() method not found. Why?

    EventCriteria.add(wmtEventCriteria.createSubcriteria('Quantifications').isEq("status", eventStatus));  // .createSubcriteria method () method not found. Why?
}
2

Where are you creating wmtEventCriteria and EventCriteria?

3

About the ‘in method not found’ there are a few like this, use .isIn() instead. iirc .or() as well as .and() have trouble too.

Just checked the source, for namespace issues like this you can prefix ‘$’ or ‘is’ to them. so $and(), $or(), etc. https://github.com/ColdBox/coldbox-platform/blob/master/system/orm/hibernate/criterion/Restrictions.cfc

4

Hi Jedd–

For the subcriteria, you’ll need to add both a projection and a method from the Subqueries class (http://wiki.coldbox.org/wiki/ORM:DetachedCriteriaBuilder.cfm#Subqueries).

You have to do this because the subcriteria builder is ultimately getting translated to a subquery in the WHERE clause. So the required projection limits the value returned from the subquery, while the method from the Subquery class signals which property in the main query the result of the subquery should be compared to.

So in your case, let’s say you want to to a subquery on the eventID property. You could do something like: