Sort by number of associations in Grails

Question

I have many Subject objects, and each Subject has many messages: Message How can I arrange all the objects of a topic based on their number of messages?

Answer 1

You can do this, but it requires two queries. This is because you need to use a group to sort by the size of the collection, but this requires that you list all of your properties. If you add or remove one request, it will break. Thus, the solution is to run one query that finds ordered identifiers, and the second - instances for these identifiers:

String hql = '''
SELECT t.id
FROM Topic t LEFT JOIN t.posts AS post
GROUP BY t.id
ORDER BY COUNT(post) DESC
'''
def ids = Topic.executeQuery(hql)
def orderedTopics = Topic.getAll(ids)

Answer 2

HQL size(). :

SELECT topic
FROM Topic topic
ORDER BY size(topic.posts)

http://docs.jboss.org/hibernate/core/3.3/reference/en/html/queryhql.html, 14.16 .