Grails createCriteria group by

Question

How can I get something like this MYSQL query with 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 order, max and sort field like this:

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

But this gets me different rows with the same 'item_id'

What can I do?

Answer 1

How about listDistinct{} ?

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