Grails groupProperty and order. How it works?

Question

I have this domain :

class Participation {
   ParticipationStatus status
}
class ParticipationStatus{
   String name
   Date creationDate
}

I create a query :

Participation.createCriteria().list{
   createAlias("status","statusAlias")
   order "statusAlias.creationDate"
   projections{
     groupProperty "id"    
   }
}

But I received an error : Caused by: java.sql.SQLException: ORA-00979: N'est pas une expression GROUP BY

I 'm working 2 days ago on this query grrrr ! ;-)

Thanks a lot

score 9 · Accepted Answer · answered Mar 07 '11 at 14:47

9

Every field you use in aggregate queries (the one using projections) should be either a groupProperty, or only an aggregate function argument (that is, in projections anyway). In this example, try

Participation.createCriteria().list{
   createAlias("status","statusAlias")
   order "statusAlias.creationDate"
   projections{
     groupProperty "id"    
     groupProperty "statusAlias.creationDate"
   }
}

answered Mar 07 '11 at 14:47

Victor Sergienko

13,115
3
57
91

Thanks. I tried this and it's working but the result is [[1,date object], [2, dateobject]] and would like receive only [1,2] (only ids) – Jonathan Lebrun Mar 07 '11 at 14:52
1

You can't, if you're using `group by`. Grouping breaks the original order of records, so you can `order` only the resulting dataset - and you need to add the date field for that. OTOH, do you need grouping at all? If `id` is Grails' `id`, then it's unique anyway. – Victor Sergienko Mar 07 '11 at 15:00

Grails groupProperty and order. How it works?

1 Answers1

Linked