Grails Pagination with Multiple Order Clauses – How To

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.

Advertisements

Populating a select box in Grails with values from database

A fairly common requirement is to populate values in a select box/drop down box with values from a database. It’s quick and easy to do in Grails.

  1. Inside your Contorller, create your query in the closure associated with your view. Eg. I’m going to create a new object that contains a list of values from that database and return it to the search.gsp view:
  2. def search = {
      def authors = Book.executeQuery("SELECT distinct b.author FROM Book b")
      [authors : authors]
    }
  3. Inside your search.gsp, add a select box that displays the list of countries:
    <g:select id="authorSelection" name="author" from="${authors}" value="" noSelection="['':'Please Select...']> 
  4. When you are reading the value back out of the form in your controller, simply use:
    params.author
  5. You’re done! Easy peasy.

EDIT: I recently found an official looking tutorial about how to do selects with grails here.

Debugging in Grails

Debugging your Grails app is just like remote debugging a regular java app.
1. Firstly in eclipse create a remote debugging configuration and click run. The default port is 5005.

Debug Config

2. Start up grails in debug mode. Do this by typing the following command:

grails-debug run-app

3. That’s it! More info here.