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.
Archived Comments
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!
Pretty much what Dan said. Cool tip.
Sharp.
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.
@Ben I've been using more HQL as it returns objects not a query recordset.
@Sam,
Oooh right; I forgot that HQL returns objects :)
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.
Oh. Interesting. I just tried getting columns in HQL and got an array of arrays...
Ah sorry, not array of structs. You are right. So you have to remember what position your select statement uses for each column.
Yeah, the array of array's is the first thing I've come across with ORM that I don't like so far...
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/...
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.
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.
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.....
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'
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.