CODEWITHSUNDEEP
hibernategrailspaginationcriteriahibernate-criteria
Vivek Sadh
Vivek Sadh

Reputation: 4268

Wrong pagination result when removing duplicate records

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 ?

Upvotes: 0

Views: 1628

Answers (3)

Deigote
Deigote

Reputation: 1761

I sadly never managed to make this work. When joins and pagination are in the mix, Hibernate is lost, and actually I think it must be: AFAIK the only way to solve the problem of having multiple rows for the same domain class instance would be to inspect the domain class and see if it has a primary key, and then aggregate the results by it. I'm not sure if that's always possible.

In any case, the way I "solved" this is by creating a separate query for evaluating the criteria, and then a query to apply the pagination (which can trigger yet another one for doing the results count).

So using the gist where the failing case is described: https://gist.github.com/deigote/549dcecdbb2a6ba80074

I would solve it by:

def matchedIds = Car.createCriteria().list([:]) {
   distinct 'id'
   createAlias('brands', 'brands', CriteriaSpecification.INNER_JOIN)
   ... // criterias here
}

def actualResults = Car.createCriteria(max: maxResults, first: firstResults).list {
  'in'('id', matchedsIds)
}

The actualResults contain the current "page" results and allow you to invoke totalCount to get the total number of results.

Upvotes: 0

Vivek Sadh
Vivek Sadh

Reputation: 4268

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.

Upvotes: 1

Dragan Bozanovic
Dragan Bozanovic

Reputation: 23562

Pagination is hard to achieve when joining with collections; the alternative is to use subqueries, as explained here.

Upvotes: 0

Related Questions