Hi, hopefully this is a straightforward question.

I'd like to order a set of query results by the size of a collection property. The FAQ tells me how to do this in HQL, but is there a way to do it using the Criteria API? For example, using the following schema:

class User
- Set<Message> messages; // one-to-many relation

... I'd like to do something similar to the following:



Is there something I'm missing?

Thanks!

Maybe this:

Sadly, that doesn't seem to quite work.

I tried something similar to this:



... and that returns a nicely ordered list of the message counts, but not the users. I even tried splitting the invocation and calling list() on the original criteria instance (created with Session.createCriteria()) but that seems to have identical behavior.

For those interested, it turned out that there was no good solution to this. Even hand-building queries via HQL was extremely painful, since in order to reconstitute the list of returned objects I had to enumerate all of the attributes of the object and its ancestors in the select statement. And then even that broke, since it didn't properly order "Users" without any "messages".

I wound up just adding a derived property (e.g. "msgCount") to the object definition that represented the thing that I wanted to order by, and inserted a formula that consisted of a blob of SQL that calculated the property, e.g.



The "msgCount" property can then be referenced like any other property in the Criteria API. This is probably not the most efficient solution, but it seems to work.

So, adding a @Formula is the only solution?

I would love to do this:

criteria.addOrder(Property.forName("size(widgets)").desc()

OR

criteria.addOrder(Property.forName("widgets.size").desc()

(of course, neither works)