how to get distinct results using Projections and Criteria

Question

I am trying to load distinct Parents using Criteria in Grails. The query is as following

Query:

def criteria = Parent.createCriteria();
        results =  criteria.list(max:params.max, offset:params.offset){
            projections{ groupProperty('id') }
            children{
                books{
                    like('title',"%book")
                    }
                }
            order("id","asc")
        }

Domain Classes

    class Parent {

    String name

    static hasMany = [children:Child]

    static constraints = {
    }
}


class Child {

        String name
        Parent parent

        static belongsTo = [parent:Parent]
        static hasMany =   [books:Book]
        static constraints = {
        }
    }


class Book {

        String title
        Child child

        static belongsTo = [child:Child]
        static constraints = {
        }
    }

Question : I am unable to get distinct Parent Rows.

Other adopted Approaches and their results: I donot know why groupProperty is not working. I have tried distinct in projections instead of groupProperty and it isnt fruitfull too!. if i use criteria.listDistinct instead of criteria.list then i am able to get distinct Parent Rows but earlier approach require to get totalCount from extra query for pagination. Therefore i am highly intersted in getting distinct Parent Rows using criteria.list

Thanks in advance

Answer 1

You can achieve the same effect as with criteria.listDistinct if you change the criteria query to include distinct root entity results transformer like this:

    results =  criteria.list(max:params.max, offset:params.offset){
        children{
            books{
                like('title',"%book")
                }
            }
         resultTransformer Criteria.DISTINCT_ROOT_ENTITY            
         order("id","asc")
    }

There is however a reason why grails does not return paged results for the listDistinct call so it might be a case to resort to an HQL query with the in operator