grails (11) vaadin (11) meteor (6) java (4) elasticsearch (3) apple (2) centos (1) cloudbees (1) google analytics (1) gradle (1) heroku (1) javafx (1) javascript (1) jdbc (1) jug (1) logback (1) logging (1) mac os (1) management (1) mongodb (1) mongolab (1) mysql (1) twitter (1) ubuntu (1)

Friday, January 20, 2012

Grails, listDistinct and pagination

A beautiful red box in Grails documentation says that pagination doesn't work when using listDistinct() method. One should rather use HQL for pagination. Well, I have wrote really huge query using createCriteria().listDistinct() and the citation below caused me almost heart attack. 
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. You can find out more information from this blog post.
I could rewrite it to HQL but the query I wrote is really heart of the system. So, it could take me few weeks to rewrite it and I don't have time and energy to do it. I am willing to scarify a bit from performance of the system. 

I have discovered the solution described below. It is not the best one from performance point of view, BUT it works and it is not causing high consumption of memory (there are thousands of records in DB nowadays and it works well). 

class ResponseService {
  def loaddef params ) {
    def idList =  Response.createCriteria().list {
      projections { distinct ( "id" ) }
      // here are your restrictions...
      // e.g. eq( 'name', parameter.name )
    }
    if( idList ) { 
      // define range startIndex..usedMax
      int idListSize = idList.size()
      int usedMax = params.startIndex + params.max - 1
      def usedIds
      if( usedMax >= idListSize ) {
        usedMax = idListSize
      }
      // select only IDs we want to fetch 
      usedIds = idList[ params.startIndex..usedMax ]
      // fetch all Responses based on selected IDs
      def results =  Response.getAll( usedIds )
      return results
    }

  }
}
These resources inspired this solution: 
TextLab for Mac
Ultimate application to validate, clean and format JSON, XML, SQL, HTML.

4 comments:

  1. Thanks for this post! Worked perfectly for me...especially converting from using createCriteria().list(max: 10, offset: 1). I started noticing duplicates and was not too thrilled when I read I was going to have to switch to HQL.

    ReplyDelete
  2. I do not think it is a good idea to fetch all the ids to idList.

    And I failed to use panigation feature to get idList. totalCount of the PagedResult is wrong, it is the result of "select count(*) from ...", not "select count(distinct(id)) from ...".

    ReplyDelete
  3. Yes, it is not good solution, but it is a solution that helps with performance at some level. Of course, this is stupid solution for the big amount of data.

    But, I don't know what exactly is wrong in your case... How did you solve it?

    ReplyDelete
  4. I also come same problem what if we make separate query for fetching results and there total count.
    Since i also agree that above solution is not very scalable since it will hit perfomance if we have large amount of data.

    ReplyDelete