CriteriaBuilder Querying Multiple Associations

Communities ColdBox HMVC
1

Hey guys,

Trying to achieve something simple with the CriteriaBuilder but clearly I’m missing something.

Let’s say I have orders with the following associations:

  • Many orders have one status
  • Many orders have one customer shipping method

Basically, I just want to query the database for all orders with a status of “Shipped” and a shipping method of “Customer Pickup”.

In OrderService.cfc:

var c = this.newCriteria();
c.withStatus().isEQ(“title”, javaCast(“string”, “Shipped”));
c.withCustomerShippingMethod().isEQ(“title”, javaCast(“string”, “Customer Pickup”));
c.maxResults(2);
dump(c.list());

However, I keep getting this error:

Application Execution ExceptionError Type: org.hibernate.QueryException : 0

Error Messages: could not resolve property: CustomerShippingMethod of: OrderStatus

Any help would be appreciated.

Thanks,

Grant

2

I believe when you use the createCriteria() or with{AssociationName}(), a new criteria is created and is rooted at the associated entity. So when you stack like you have in your example, the second with{AssociationName}() expects to begin from the context of currently rooted entity, which in this case will be OrderStatus. In essence, it’s trying to associate CustomerShippingMethod with OrderStatus, even though both of them are associated at the Order entity.

If you use createAlias(), you should be able to accomplish what you’re after. Something like:

var c = this.newCriteria();
c.createAlias( “Status”, “status” )
.createAlias( “CustomerShippingMethod”, “csm” )
.isEq( “status.title”, javaCast(“string”, “Shipped”) )
.isEq( “csm.title”, javaCast(“string”, “Customer Pickup”) )
.maxResults( 2 )
.list()

Be sure to check the SQL that’s generated to make sure the query is being constructed as you expect.

Hope this helps!

3

Thanks Joel. Worked perfectly!

4

Excellent, happy to help!

5

Hi Grant

Just for reference
1: http://wiki.coldbox.org/wiki/ORM:CriteriaBuilder.cfm
2: http://wiki.coldbox.org/wiki/ORM:DetachedCriteriaBuilder.cfm

We have documented Hibernate references link as well.

Thanks