how to get distinct results using Projections and Criteria
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
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