Wrong pagination result when removing duplicate records

Question

I am using the criteria below with pagination parameters. There were some duplicate records generated due to join so I used setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY). It removed the duplicate record but it seems that it removes the duplicate records after applying pagination.

For example: If offset is 20, max is 10. It means it should fetch 20-30 record. But suppose records 28,29 and 30 are duplicate so they are removed and in the page only 20-27 records are displayed. So the third page displays only 20-27 records even though these records are not last.

  return Question.createCriteria().list(offset: offset,max: max) {
        createAlias("questionHistory","qh")
        if(createdStartDate!=null){
            ge('createdDate',createdStartDate)
        }
        if(createdEndDate!=null){
            le('createdDate',createdEndDate)
        }
        if(folderId>0){
            eq('folder.id',folderId)
        }else if(itemBankId>0){
            or{
                folders.each {
                    eq('folder.id',it.id)
                }
            }
        }
      ....
      ......
       if(authorIds?.size()>0){
            'in'("qh.changedBy.id",authorIds)
        }
      ..........
      ..................
      ....................................
       setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
    }

I know that the reason is because first the criteria is executed and then the duplicate records are removed. Is there any way to fetch the next records(if any) equal to the number of duplicate records found ?

Answer 1

I have solved it. Instead of using pagination params in list() I used setFirstResult/setMaxResults. But the disadvantage is that it does not give 'totalCount' value so a separate query needs to be fired for it.

Solution :

setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
setFirstResult(offset)
setMaxResults(max)

It first removes duplicate then applies pagination giving correct result.