distinct result in Hibernate Query in grails with pagination using association

Question

I have three class interlinked with each other.

Initially I build query without pagination and all search results were fine. But now I need pagination and don't want to disturb my initial query pattern. Is there any way I could get distinct results.

Class Department{
    int id;
    String name;
    static hasMany = [courses:Courses]
}

Class Courses{
    String courseName;
    String courseCode;
    static hasMany = [student:Student]
    static belongsTo = [department:Department]
}

Class Student{
    String studentName;
    String address;
    static belongsTo = [courses:Courses]
}


//controller
def list = Department.createCriteria.listDistinct{
    if(params.id){
         and{eq{"id",params.id}}
    }
    and{
        courses{
            if(params.courseName){
                  and{eq("courseName",params.courseName)}
            }
        }
        and{
            student{
                 if(params.studentName){
                         and{eq("studentName",params.studentName)}
                 }
            }
        }
    }
}

I could not gave you the actual tables and domains, but the relation is pretty much the same as above. It worked really fine for dintinct results but couldnot paginate. I have tried a number of solution but it returns error. I have not recorded any errors till now. I have came to know listDistinct can not be used for pagination parameters. and list doesnot provide distinct parameters.

I tried projection but couldnot retrieve all the attributes as before. Is there any solutions to it. As I need to search from all possible attributes with all the realtion from any of the three tables. Do I need to switch all my query to another method?

Answer 1

I had a hard time with similar task some time ago - getting criteria, pagination and distinct to work together, and the solutions is: 1. use list() instead of listDistinct() 2. use maxResults and firstResult for pagination 3. use projections { distinct 'id' } for getting distinct results 4. and after getting the list of ids, use getAll() method to retrieve actual objects

so joining it it would be:

def ids = Department.createCriteria().list() {
    projections {
        distinct 'id'
    }
    maxResults params.max
    firstResult params.offset

    if(params.id){
         and{eq{"id",params.id}}
    }
    and{
        courses{
            if(params.courseName){
                  and{eq("courseName",params.courseName)}
            }
        }
        and{
            student{
                 if(params.studentName){
                         and{eq("studentName",params.studentName)}
                 }
            }
        }
    }
}
return Department.getAll(ids)

(code not tested now)

Answer 2

According to the documentation

The listDistinct() method does not work well with the pagination options maxResult and firstResult. If you need distinct results with pagination, we currently recommend that you use HQL.