Grails findAll with sort, order, max and offset?

grails, grails-orm, groovy

Solution

HQL doesn't support sort and order as parameters, so you need to include the "order by" as part of the HQL expression

def books = Book.findAll("from Book as b where b.approved=true"
  + " order by b.dateCreated desc", [max: max, offset: offset])

(or in this case just use `Book.findAllByApproved(true, [...])` instead of HQL).

So if the sort and order are variables you need a trick like

def books = Book.findAll("from Book as b where b.approved=true"
  + (params.sort ? " order by b.${params.sort} ${params.order}" : ''), 
  [max: max, offset: offset])

Problem

I want to integrate sort, order, max and offset in a findAll query. The following works fine: ``` def books = Book.findAll("from Book as b where b.approved=true order by b.dateCreated desc", [max: max, offset: offset]) ``` But what I want is: ``` def books = Book.findAll("from Book as b where b.approved=true", [sort: 'dateCreated', order: 'desc', max: max, offset: offset]) ``` This does not work. How do I have to rewrite this?

Original source