How to do Group By in grails to order by Count(*)

Question

How do I translate:

SELECT COUNT(*) AS `count`, `a` FROM `b` GROUP BY `a` ORDER BY `a`

into grails or gorm query?

Answer 1

I would try

def c = b.createCriteria()
def results = c {
   projections {
      groupProperty("a")
      rowCount()
   }
   order("a")
}

Note this is untested.

Answer 2

Since grails 1.2 you can create aliases and order by the created alias.

See https://cvs.codehaus.org/browse/GRAILS-3875 and https://cvs.codehaus.org/browse/GRAILS-3655 for more details.

Applied to your own code, the HQL query would be :

def c = b.createCriteria() 
def results = c { 
  projections {
    groupProperty("a")
    count("a"), 'myCount' //Implicit alias is created here !
  }
  order 'myCount'
}

Answer 3

working in grails 1.2.1

def c = C.createCriteria()
def pl = c.list {
    projections {
        countDistinct 'id', 'myCount'
        groupProperty 'a'
    }
    order ('myCount', 'desc')
}

the answer is for example

[[10,a3],[2,a1],[1,a2]]