How can I do this query in Grails?

Question

I have two domain classes:

class User {
    String nickname
}

and

class Episodes {
    String name
    User sentBy
}

I want to make a Ranking about who has sent more episodes.

So I did this query, and gives me the result that I want.

SELECT user.nickname, count(episodes.id) total
FROM user
JOIN episodes on user.id = episodes.sent_by
GROUP BY user.nickname
ORDER BY total DESC

What's the best way to run this query in my Controller? How?

Answer 1

I would use HQL for this:

def results = User.executeQuery(
   'SELECT e.sentBy.nickname, count(e) as total ' +
   'FROM Episodes e ' +
   'GROUP BY e.sentBy.nickname ' +
   'ORDER BY total desc')

The result will be a list of Object[] arrays, with the 1st element being the nickname and the 2nd being the count, e.g.

for (Object[] row in results) {
   println "User ${row[0]} has ${row[1]} episodes"

}