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?

That shouldn't be run in your controller, it should go in a service. — doelleri, Dec 13 '12 at 22:04
http://grails.org/doc/latest/ref/Domain%20Classes/createCriteria.html — James Kleeh, Dec 14 '12 at 02:20
@doelleri - Queries are fine in controllers, but updates should be transactional and in a service. — Burt Beckwith, Dec 14 '12 at 06:26

score 0 · Accepted Answer · answered Dec 14 '12 at 06:44

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"

}

score 0 · Answer 2 · edited May 23 '17 at 12:13

0

There is an answered question about your concern:

Using groupProperty and countDistinct in Grails Criteria

edited May 23 '17 at 12:13

Community

1
1

answered Dec 14 '12 at 09:16

coderLMN

3,076
1
21
26

How can I do this query in Grails?

2 Answers2