Sorting on a Relationship

So, I’ve got an orm entity User with a one-to-one relationship with an entity Stats.

Stats has a property totalPoints.

I want to return all users sorted on the value of totalPoints…

Any pointers on how to do this?

Obviously with the actual attributes of the User sorting is easy… UserService.list(sortOn=“lname desc”)… it would be amazing if I could do something like sortOn=“stats.totalPoints”, but alas, it’s not that easy.

I’m thinking I need to do something with the criteria builder but haven’t been able to get anything to work.

Any help would be greatly appreciated!


You can use HQL querying for that, here’s an example (I’m doing this on the fly so some tweaking may be needed):

users = ormExecuteQuery(“select u from User u join u.stats s order by s.totalPoints”);

Brian Kotek has some great blog posts with additional information on HQL, checkout the first post in a series here:

Also, if “stats” is lazy loading, you can alter the fetching strategy for this method.

Borrowing Phil’s code…
users = ormExecuteQuery(“select u from User u join fetch u.stats s order by s.totalPoints”);

Depending upon the intended use of “stats” after you’ve returned the users, this may result in a more efficient generated SQL query.

Jason Durham

You could also have a calculated property on user for total points… something like…

component displayname=“user” {
property name=“totalpoints” type=“numeric” default=“0” formula=“select sum(s.totalPoints) from stats s where s.userid = userid”;

Then you can just UserService.list(sortOn=“totalpoints desc”)

By the way, it might have just been a post-typo, but the argument is not sortOn, it is sortOrder.
UserService.list(sortOrder=“totalpoints desc”)

Thanks guys. This is incredibly helpful.

I ended up making criteria work via the following.

var c = UserService.createCriteria();
var users = c.withStats().order(‘totalPoints’).list();

It’s amazing to me how many different options we have to do this :slight_smile:



Here is some more info which deals with ORM objects sorting.


Criteria builders to me is my favorite. So elegant.