Criteria search

Question

I ran into some problems while trying to count items.

Imagine the following domain classes

class Book {
  String name
}
class Author {
  String name
  static hasMany = [books:Book]
}

How do I get a list of Authors sorted by number of Books?

here's my try:

def c = Author.createCriteris()
c.list {
  projections {
    count 'books', 'numBooks'
    groupProperty 'id'
  }
  order 'numBooks', 'desc'
}

but somehow I get only unusable results... and I don't know how to join the Author objects to the rsult list.... :-(

Answer 1

got something!

I still don't know how to do it with a criteria, but by switching to HQL, I succeeded.

So if someone comes up with a criteria solution, he will still get the bonus for the correct answer :-)

here is my query:

Author.executeQuery("""
    select 
        a, size(a.books) as numBooks 
    from 
        Author a 
    group by 
        id 
    order by 
        numBooks DESC
""",[max:20])

This query isn't efficient, since it fetches all Authors in a loop, but that's ok for now.