Groovy/Grails: Set max parameter to get certain number of results of a complete set

Question

I am using the max parameter in my query to specify a certain number of results so I can load them 20 at a time. However I am sorting them in a way where they are sorted: newest first, then by type, then by name. If I get the complete results set (max = 0), it sorts fine and I get the expected results. However If I want to get 20 at a time, I am not getting the first 20 results of the previous result like I would expect. How can I set the max parameter to get the first twenty results of the completed set of results? Thanks for any help in advance.

Here's an example of the query:

def productXML = 
     Product.findAll("from Product as p where p.is_active = 1 and p.type = :type 
                      ORDER BY p.${params.sort} ORDER BY p.is_new, p.type, p.name DESC", 
                      [type: type], [max: params.max, offset: params.offset])

Answer 1

If you don't mind using a criteria query instead, the following should work

List<Product> queryResults = Product.withCriteria {
  eq('is_active', 1)
  eq('type', type)

  order(params.sort, "asc")
  order("is_new", "asc")
  order("type", "asc")
  order("type", "desc")

  maxResults(params.max)
  firstResult(params.offset)
}

I abhorr HQL and adore criteria queries, but that's a matter of personal preference.