Photo from Chile

Filtering Collections in ColdFusion ORM

A question was asked on the cf-orm-dev Google Group about returning just a subset of objects that make up a collection. Specifically, the developer had a Page object and a Content object with a many-to-many relationship. A simplified version of the objects is:

Page.cfc:

view plain print about
1component persistent="true" {
2    property name="pageId" fieldtype="id" generator="native";
3    property name="title";
4    property name="contents" fieldtype="many-to-many" cfc="content"
5        singularname="content" linktable="page_content";
6}

Content.cfc:

view plain print about
1component persistent="true" {
2    property name="contentId" fieldtype="id" generator="native";
3    property name="column" ormtype="integer";
4    property name="pages" fieldtype="many-to-many" cfc="page"
5        singularname="page" linktable="page_content" inverse="true";
6}

Each Page can have multiple pieces of Content, and each Content block appears in a particular column on that page. Each Page can have multiple Content objects in any given column. The developer was wondering if he could do something like:

view plain print about
1Page = entityLoadByPK("Page",1);
2column1Contents = Page.getContents({column=1});

Which would return all of the Content objects assigned to that Page, but only those that are in column 1 (which is a property of the Content object).

At first glance this doesn't seem possible. Certainly that syntax is not available (passing criteria into a getter on a collection). There are other ways to get that data, however, including:

  • Use an HQL query with criteria that returns a set of Content objects for a given Page and column. The downside to that is that if you had 3 columns Hibernate would end up issuing 3 SQL queries.
  • Create a custom method which would loop through all of the Content objects for the Page, and only return those that match a particular column, the value of which could be passed into the method via an argument.

I had a feeling that there yet another way to do this, using the features of Hibernate, and upon further reflection I realized that this would be a perfect candidate for a Hibernate filter. Hibernate filters are very cool, and allow you to do a bunch of neat things, but for the purposes of this post we're just going to look at using them to filter collections. Basically, a filter allows you to create a subset of data by specifying criteria. In this case we want to create a subset of all of the Content objects that belong to a Page by specifying a value for the column property of the Content objects. The only slightly tricky part about working with Hibernate filters is that they are not directly accessible via built-in ColdFusion functions; we need to work directly with Hibernate to create them and work with them. There are two ways to create a Hibernate filter:

  • Define it in an hbmxml file, which requires that you write, or generate, an hbmxml file for your component, and then edit that hbmxml file. As this is a bit more complicated I won't be covering that in this blog post, but I will in a follow-up post.
  • Use the createFilter() method that is available in the Hibernate Session object. This is what we'll look at next.

There is more explanation than code required, so I'm going to start with the code and then walk through it. Here's a simple example of creating a filter that gives us a subset of Content objects for a given Page:

view plain print about
1Page = entityLoadByPK("Page",1);
2column1Contents = ormGetSession().createFilter(Page.getContents(),"where column = 1").list();

Because the createFilter() method exists in the Hibernate Session object, the first thing we have to do is get access to the current Hibernate Session. As you can see that's as simple as calling ormGetSession(). We can then call createFilter() to create the filter. It accepts two arguments, the first is the collection, which in this case is Page.getContents() and the second is an HQL query string. The HQL query string has an implicit FROM clause, so we can leave that out. What we want to do in this situation is to only retrieve those Content objects with a column value of 1, so we just specify that in the HQL WHERE clause. Finally, we call the list() method on the filter object, which returns the result to us.

Now that's pretty simple, but it's not very flexible: it only gives us all of the Content objects for column 1. What if we want column 2, column 3, etc.? Let's tackle that issue, but first let's put this code where it really belongs, in the Page object itself. Let's add a method called getColumnContents() to our Page object. Here's the new Page.cfc:

view plain print about
1component persistent="true" {
2    property name="pageId" fieldtype="id" generator="native";
3    property name="title";
4    property name="contents" fieldtype="many-to-many" cfc="content"
5        singularname="content" linktable="page_content";
6        
7    function getColumnContents(column) {
8        return ormGetSession().createFilter(Page.getContents(),"where column = #arguments.column#").list();
9    }
10}

That was easy. Now we can ask our Page object for all of the Content objects in any given column by calling the getColumnContents() method. The only problem with that code is that it potentially opens us up to SQL injection attacks, as described by Jason Dean in this informative blog post. Luckily there's an easy way to solve that problem; our filter provides us with methods of specifying parameters to be used by the HQL query. Here's what a new, safer version of this method would look like:

view plain print about
1function getColumnContents(column) {
2    return ormGetSession().createFilter(Page.getContents(),"where column = :column").setInteger("column",arguments.column)list();
3}

First we replace our inline variable with a named parameter, :column. Next, because our column property contains integer values, we use the setInteger() method to set a value for that parameter, thusly protecting ourselves from evil doers. And with that we have a method in our Page object that can be used to return all of the Content objects that belong in a certain column. The code to use it would look something like this:

view plain print about
1Page = entityLoadByPK("Page",1);
2column1Contents = Page.getColumnContents(1);

Unfortunately, after I wrote this post I did some testing and it looks like Hibernate is going to execute a new SQL query each time that method is called, even if the entire collection of Content objects has already been loaded. I was disappointed to see this, and I'm going to experiment with the other way of creating filters (in the hbmxml file) to see if they will make use of data that is already cached in the object. This means that this approach is really no better than simply writing an HQL query in your object that will return all of the Content objects for a given column, which is something I hoped to avoid because it could result in numerous queries. I think I still prefer the design and syntax of using this filter method over writing out an entire HQL query, but there doesn't appear to be any performance advantages.

TweetBacks
Comments
Maybe there 'where' attribute can help: http://help.adobe.com/en_US/ColdFusion/9.0/Develop...
# Posted By Henry Ho | 1/19/10 4:36 PM
In your created method getColumnContents, the line:

return ormGetSession().createFilter(Page.getContents(),"where column = :column").setInteger("column",arguments.column)list();
}

generates an error 'Page not defined'. You would only need to call getContents() if the method is defined as-is.

Other than that - this saved me quite a bit of time and works great for my particular situation.

-Cory
# Posted By Cory Miller | 4/28/10 12:29 PM