Grails Creates Criteria Group

Question

How can I get something like this MYSQL query using createCriteria in grails?

SELECT * FROM engine4_user_fields_values WHERE field_id = 31 OR field_id = 24 GROUP BY item_id; 

It works fine if I use something like this:

 def items = items_c.list{ 'in'('fieldId',field_ids) projections{ groupProperty("itemId") } } 

But I need to define the order, max and sort field as follows:

 def items = items_c.list(max:5, sort:"itemId", order:"desc"){ 'in'('fieldId',field_ids) projections{ groupProperty("itemId") } } 

But it gives me different lines with the same "item_id"

What can I do?

Answer 1

What about listDistinct {}?

http://grails.org/doc/latest/guide/5.%20Object%20Relational%20Mapping%20 (GORM) .html # 5.4. 2 Criteria

Answer 2

Use projection for the first result,

  lists= items.createCriteria().list(){ projections { order ("ItemId") } } 

or executeQuery ("your request here"); with distnict