1

i am using Grails Criteria to pull results for a specific domaini want to ask whether is it possible to have group property in order clause or in other words, how can i write following mysql query using Grails criteria ?

select 
       h.name,
       count(h.id) 
from my_table as h 
group by h.name 
order by count(h.id) desc;

so far grails criteria query looks like 

def results =  MyObject.createCriteria().list{

 projections{
     groupProperty('name')
     countDistinct('id')

  }
   order(???,'desc')
}

Thanks in advance Rehman

Rehman
  • 3,908
  • 6
  • 28
  • 29
  • 2
    answer was quite easy, one need to create alias for count function e.g ` select h.name, count(h.id) as countDist from my_table as h group by h.name order by countDist desc;` and grails criteria for aforementioned query ` def results = MyObject.createCriteria().list{ projections{ groupProperty('name') countDistinct('id', 'countDist') } order('countDist ','desc') }` cheers – Rehman May 12 '11 at 11:20
  • Might want to add this comment as the answer. – Pat May 13 '11 at 16:44

1 Answers1

2

Just encountered this problem. Just to clarify Rehman's answer.

 def results =  MyObject.createCriteria().list {

    projections {
       groupProperty('name')
       countDistinct('id', 'idDistinct')
    }
    order('idDisctinct','desc')
 }
ibaralf
  • 12,218
  • 5
  • 47
  • 69