ColdFusion 9 ORM Tip - Count related entities

This post is more than 2 years old.

Here is a simple (or so I thought) problem: Given that an entity has a related property, how can you get a count of the objects? Imagine a Group entity with a One-To-Many to Members (Groups have many Members). How would you report on the number of members in a group?

You could simply get them and count them:

<cfset totalMembers = arrayLen(group.getMembers())>

However this is a bit wasteful if you just want the count. I tried what was suggested in the Hibernate docs but had no luck getting the code to work. Thankfully Rupesh Kumar of Adobe helped me out. When you have related properties, you can get the count by using the size property. So for example:

<cfset hql = "select g.name, g.members.size as total from group g order by g.members.size desc"> <cfset r = ormExecuteQuery(hql)>

In this code I get the name and the size of the members. Notice I can also order by the size as well. This returns an array of structs containing the name and member size for each group. You can even get fancier. My groups actually have members, moderators, and admins. So to get the count I can do:

<cfset hql = "select g.name, g.members.size+g.moderators.size+g.admins.size as total from group g order by (g.members.size+g.moderators.size+g.admins.size) desc"> <cfset r = ormExecuteQuery(hql)>

Obviously to get the count for one group you would get rid of the order by and use a where clause instead.

Raymond Camden's Picture

About Raymond Camden

Raymond is a senior developer evangelist for Adobe. He focuses on document services, JavaScript, and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can even buy me a coffee!

Lafayette, LA https://www.raymondcamden.com

Archived Comments

Comment 1 by Dan Vega posted on 12/8/2009 at 10:00 PM

Awesome tip. As you stated its an incredible waste of resources to grab a list of members based on the group and then count them, this is much better. Thank You!

Comment 2 by Sam Farmer posted on 12/9/2009 at 1:36 AM

Pretty much what Dan said. Cool tip.

Comment 3 by John Allen posted on 12/9/2009 at 3:43 AM

Sharp.

Comment 4 by Ben Nadel posted on 12/9/2009 at 4:25 AM

It's going to be interesting to see if people start writing HQL more than they write SQL? I guess once you start playing with HQL, it becomes more second nature.

Comment 5 by Sam Farmer posted on 12/9/2009 at 5:17 AM

@Ben I've been using more HQL as it returns objects not a query recordset.

Comment 6 by Ben Nadel posted on 12/9/2009 at 6:14 PM

@Sam,

Oooh right; I forgot that HQL returns objects :)

Comment 7 by Raymond Camden posted on 12/9/2009 at 7:01 PM

No, HQL _can_ return objects. If you ask for specific columns, you get structs. In both cases the core result is an array, EXCEPT when you specify unique result.

Comment 8 by Sam Farmer posted on 12/9/2009 at 9:11 PM

Oh. Interesting. I just tried getting columns in HQL and got an array of arrays...

Comment 9 by Raymond Camden posted on 12/9/2009 at 9:37 PM

Ah sorry, not array of structs. You are right. So you have to remember what position your select statement uses for each column.

Comment 10 by Sam Farmer posted on 12/11/2009 at 2:39 AM

Yeah, the array of array's is the first thing I've come across with ORM that I don't like so far...

Comment 11 by Jon Hartmann posted on 12/12/2009 at 1:19 AM

This looks like the answer to one of the questioned I posted on my blog back near Thanksgiving. Basically, I found some relatively simple database tasks that just stumped me when trying to figure out how to do them with the ORM/HQL system. Now if only you can solve to other ones :)

http://www.jonhartmann.com/...

Comment 12 by Jon Hartmann posted on 12/12/2009 at 1:20 AM

Err, link got mangled: http://tiny.cc/550DQ

I figure that this count could could be abstracted into a reusable method on the entity itself... I'll have to look into that.

Comment 13 by Scott Stroz posted on 2/12/2012 at 12:05 AM

I am likely doing something wrong, but I am unable to get this to work.

If there are no related items (in my case I have an 'Account' object that can have multiple Users). If an account has no users, the result of my HQL is an empty array.

Another issue that may arise is that this will only work on persisted objects. If I add a user to an account and then try this method without persisting the account, I get the same results.

Comment 14 by Scott Stroz posted on 2/12/2012 at 12:09 AM

Just tested...if I persist the Account object, the result of the HQL is what I expect - a array with one element who's value is '0'.

Carry on...nothing more to see here.....

Comment 15 by Laura Hansen posted on 1/17/2013 at 1:20 AM

How would you go about adding a condition for the items you are wanting to count?

Say you want to find how many members in the group are female?

you can't just go:
Where g.members.gender = 'female'

Comment 16 by Raymond Camden posted on 1/17/2013 at 1:58 AM

I don't think you're going to do that in _one_ HQL statement. But it shouldn't be hard to do manually, and even easier to add a bit of caching to it. Unfortunately, I haven't used CF ORM in a while. I'm rusty.