ColdFusion ORM: Filtering on Composite Objects

I've had the need a few times lately to return a subset of ColdFusion ORM objects. In this case EntityLoad() might not always be the way to go. This post will hopefully help those new to ORM determine the appropriate action.

First, lets chat about entityLoad(). EntityLoad() looks like the perfect way to query for a collection of objects.  Given an ORM object of type Pig, consider the following definition:

<cfcomponent displayname="Pig" persistent="true">
    
    <!--- properties --->
    <cfproperty name="id" fieldtype="id" type="numeric" unsavedvalue="-1" />
    <cfproperty name="color" type="string" />
    <cfproperty name="age" type="numeric" />
    
    <cfproperty name="breed"
          fieldtype="many-to-one" cfc="Breed"
          fkcolumn="breedid" lazy="true" />
    
</cfcomponent>

It's easy to use entityLoad() to load up all pigs that are pink:

entityLoad('Pig', {color='Pink'});

Now notice that each Pig has a breed.  Let's say we want to get all pigs of breed 'Yorkshire'.  Here's where entityLoad() falls on its face.  If we tried to use entityLoad() it would look like this:

entityLoad('Pig', {breedid=1})

We get the following error:

Property BREEDID not present in the entity. 

This is because entityLoad() is a convenience function that only works against defined properties.  To entityLoad(), breedid is not a property.  Instead, Breed is the property, being of type Breed.  To get around this we can use HQL instead.  HQL is very easy to understand if you know SQL, and very easy to execute.  Please refer to my last blog entry ColdFusion ORM: Using HQL for a primer on HQL with ColdFusion ORM. For the examples below we will be using ORMExecuteQuery() as opposed to <cfquery>

Filtering the objects to get all Pigs which are Yorkshires, we can do this:

ormExecuteQuery("from Pig where breedid = 1");

ormExecuteQuery() returns an array of matching objects.  This is the case unless we are using aggregate functions like sum() or count().  In that case you get an array of arrays.  See the last section of ColdFusion ORM: Using HQL for a primer on HQL for more details on how aggregate function results are returned.

Instead of querying on a property of Pig itself, we can even query against properties of the composite object Breed:

ormExecuteQuery("from Pig pig where pig.breed.name = 'Yorkshire'");

One more thing, HQL also allows us to use alternate operators.  We can then use ORMExecuteQuery() to do things like:

ormExecuteQuery("from Pig where age < 2");