Grails findAll with sorting, ordering, max and offset?

Question

I want to integrate sorting, ordering, max and offset in a findAll query. The following works great:

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

But I want:

 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 rewrite this?

Answer 1

HQL does not support sorting and ordering as parameters, so you need to include "ordering from" 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 sorting and ordering 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]) 

Answer 2

Using the where query for me:

def books = Book.where{approved == true}.list(sort: 'dateCreated', order: 'desc', max: max, offset: offset)

Or with parameters directly from the page:

def books = Book.where{approved == true}.list(params)

Answer 3

Use "findAllBy" because it supports sorting and ordering.

 def results = Book.findAllByTitle("The Shining", [max: 10, sort: "title", order: "desc", offset: 100]) 

Click here for details.

Answer 4

I assume that you are invoking a selection of books in the controller or service class.

If you call this from a controller action, then the "params" magic variable is already available to you. For example, if you request a page as follows

 book/list?max=10&offset=2 

then "params" will already have these values ​​automatically displayed.

You can add several items to the parameter map as follows

 params.sort = "dateCreated" params.order = "desc" 

Once you create your parameters as you wish, you can use the Grails dynamic query as follows

 def books = Book.findAllByApproved(true, params) // use "books" variable as you wish