[Coldbox 3.6]: Dynamic Finders / Criteria Builder and Associations

Communities ColdBox HMVC
1

Hi guys,

I’m fairly new to searching in ORM, in particular leveraging some of the ORM searching features in ColdBox.

Is it possible to use the 3.5.3 Dynamic Finders feature - http://wiki.coldbox.org/wiki/WhatsNew:3.5.3.cfm - to subquery an association in an entity or would I need to you CriteriaBuilder?

I have an entity called Location which has a one to one relationship to Geofence, and a one-to-one relationship with Partner. In my handler (or service) I want to do a query to search for a locationID filtered by FK_PartnerID and FK_GeofenceID.

What is the best way to accomplish this?

Here is my model:

component persistent=“true” table=“Location” extends=“BasePersistentObject” cachename=“MyCache” cacheuse=“transactional”

{
property name=“locationid” fieldtype=“id” generator=“native”;

property name=“name” column=“locationname” type=“string” ormtype=“string”;
property name=“description” type=“string” ormtype=“string”;
property name=“partner” fieldtype=“many-to-one” cfc=“platform.common.model.Partner” fkcolumn=“fk_partnerid” missingrowignored=“true”;
property name=“geofence” fieldtype=“one-to-one” cfc=“platform.common.model.GeoFence” fkcolumn=“fk_geofenceid” singularname=“geofence” cascade=“all-delete-orphan” missingrowIgnored=“true”;
property name=“active”;

}

Thanks.

Nolan

2

I don’t believe that the Dynamic Finders support relationships. However, it is pretty easy to accomplish this with associations. http://wiki.coldbox.org/wiki/Extras:CriteriaBuilder.cfm#Associations

For example, you could do something like:

var c = ORMService.newCriteria( “Location” );
c.createAlias( ‘Geofence’, ‘geo’ ).isIn( ‘geo.GeofenceID’, listOrArrayofGeofenceId’s… );
var result = c.list();

or

var c = ORMService.newCriteria( “Location” );
c.createAlias( ‘Geofence’, ‘geo’ ).isEq( ‘geo.GeofenceID’, someGeofenceIDvariable );
var result = c.list()

You can add additional associations (which ultimately become JOINs in SQL), and even create associations on top of associations.

c.createAlias( “Geofence”, “geo”).createAlias( “geo.Partner”, “p” ).isIn( “p.PartnerID”, … )

As you play with this, I’d encourage you to be logging the SQL generated by Hibernate. Seeing the SQL result from what you’ve plumbed together with the CriteriaBuilder object will help it make much more sense, and is invaluable for tweaking CriteriaBuilder to get precisely what you want.

3

Hi there,

Thanks for the tips.

This essentially looks like what i'm trying to achieve (at a minimum)....

var c = ORMService.newCriteria( "Location" );
c.createAlias( 'Geofence', 'geo' ).isEq( 'geo.GeofenceID', 187 );
var result = c.list();

However when I run this I get the following error:

could not resolve property: Geofence of: Location

I enabled ORM Logging in my Application.cfc however there are so many
relationships in the particular model(s) it's hard to determine where
it's failing. I can post the terminal output in a GIST if it helps.
The above does seem pretty simple though as it relates to my model.

Any ideas?

Thanks.

Nolan

4

Try using the same case as your property name in the alias.

5

Hi again,

I changed the case to match:

var c = ORMService.newCriteria( “Location” );

c.createAlias( ‘geofence’, ‘geo’ ).isEq( ‘geo.GeoFenceID’, 187 );

var result = c.list();

…and the resulting error is now:

java.lang.String cannot be cast to java.lang.Integer on Line 111 of coldbox/system/orm/hibernate/CriteriaBuilder.cfc

Any further ideas?

BTW, I'm on Adobe CF 9.0.1 on Apache/OSX

Thanks!

Nolan
6

You’ll need to java cast your criteria value to the correct type–something like Javacast(“int”, 187)

7

FYI, as you use CriteriaBuilder more, you’ll run into this more and more, so unless its a string, it’s a good habit to do it for all criterias

8

That worked.

Good to know that I’ll need to cast when using criteriaBuilder. Luis, noting this in the docs/wiki would be beneficial.

Cheers,
Nolan

9

Glad it worked!

10

The note has been added. Best of luck with CriteriaBuilder, and please don’t hesitate to reach out if you have more questions.

Thanks!