Tuesday, February 15, 2011

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" />

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");


  1. Cant you just do

    variables.thisBreed = entityLoadByPk("breed",1)
    entityLoad('Pig', {breedid = variables.thisBreed})

  2. Sorry I mean.

    variables.thisBreed = entityLoadByPk("breed",1)
    entityLoad('Pig', {breed = variables.thisBreed})

  3. @namtax If I did:
    thisBreed = entityLoadByPk("breed",1)

    I'd probably just use: thisBreed.getPigs() via the bidirectional relationship for efficiency.

    To get the other example working where we get by the breed name, we could use:
    thisBreed = entityLoad("breed", {name='Yorkshire'}, true);

    That's the thing with these ORM functions. I can do the same job plenty of different ways. Not to promote premature optimization, but it really comes down to which one will take the least performance hit. In most apps it won't matter. In my case, the impetus for needing sorting was the ability to filter on multiple composite objects at once, for which HQL was the way to go.

  4. Important to note, as I was just referring back to this...

    When using dot notation to get to a composite object property, the case MUST match the case defined on the property in the CFC.

  5. how to filter output using Not Equal,

    lets say that i want to output data from a table where a filed is not null or empty string.

  6. Thanks buddy, you saved me lot of hours....