Sorting on a Relationship

Communities ColdBox HMVC
1

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!

2

Ben,

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: http://www.briankotek.com/blog/index.cfm/2010/9/9/Using-ColdFusion-ORM-and-HQL-Part-1

3

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

4

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”)

http://wiki.coldbox.org/wiki/Extras:BaseORMService.cfm#list

5

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:

Cheers,
Ben

6

Hi,

Here is some more info which deals with ORM objects sorting.http://cookbooks.adobe.com/post_How_to_sort_an_array_of_objects_or_entities_with_C-17958.html

Thanks
Sana

7

Criteria builders to me is my favorite. So elegant.