Grails Pagination with Multiple Order Clauses – How To

Posted by: nerdgerl on: August 25, 2009

• In: Uncategorized
• Comment!

There is a known bug in grails where you can’t have multiple order by’s when returning a PagedResultList. When talking about the .createCriteria().list() method it seems you can only specify the order by inside the .list() method and not in the builder body itself even if only ordering by one values.

This is very annoying as I think multiple order by’s would be a common requirement of a lot of apps. Below is my work around to this bug. I’ll investigate providing a patch to fix this in the near future.

Let’s start by taking a look at three ways you might use to query the database:

Our domain class looks like this:

@Entity
@Table(name = "tbl_book")
public class Book {

@Id
@Column(name = "bookId")
private int id;
private String author;
private String title;
private Date releaseDate;

// getters and setters

1. Dynamic Methods:

   def results = Book.findAllByAuthor("Marsden", [max:10, offset:0, sort:"releaseDate", order:"desc"])

   The above method would return a PagedResultList ordered by releaseDate descending, but what if we want to order by venue as well? We can’t! Moving on…

2. def results = Book.createCriteria()

            def results = Book.createCriteria().list(
                max: params.max,
                offset: params.offset) {
                and {
                    eq('author', 'Marsden')
                    order('releaseDate', 'desc')
                    order('title', 'asc')
                }
            }  

   The above thows a runtime sql exception, you can get it to work only by including the order in the .list() function and only if you include just one order statement, further details of the bug here. Sigh…

3. def results = Book.withCriteria()

           def results = Book.withCriteria() {
               maxResults(params.max?.toInteger())
               firstResult(params.offset?.toInteger())
   
               and {
                   eq('author', 'Marsden')
                   order('releaseDate', 'desc')
                   order('title', 'asc')
               }
           }
   

   The above looks fine except it doesn’t return an instance of PagedResultList which means we don’t have access to the results.totalCount method which gives us the total number of results needed for pagination in the g:pagination tag in the view. Still no dice…!

Solution

Use query method 3, Book.withCriteria() and then create a seperate query to return the total number of results eg:

        def rowCount = Book.createCriteria().get() {
            projections {
                rowCount()
            }
            and {
                eq("author", params.author)
            }
        }

rowCount should then be passed to your view so the pagination tag can use it:

            <div class="paginateButtons">
                <g:paginate total="${rowCount}" />
            </div>

This solution is really ugly. Im not a fan of having to execute the query twice just to get the total count. I really hope this bug is fixed soon, or someone tells me im missing something fundamental

Nabble questions on this here and here.

Tags: code, grails