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 load( def 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: 

• http://floledermann.blogspot.com/2007/10/solving-hibernate-criterias-distinct.html
• http://stackoverflow.com/questions/3905476/gorm-createcriteria-and-list-do-not-return-the-same-results-what-can-i-do  

TextLab for Mac

Ultimate application to validate, clean and format JSON, XML, SQL, HTML.