Grails Pagination and CriteriaBuilder

A colleague of mine and I have agreed on an analogy for Grails development: it’s like driving on the Autobahn, with serious sets of speed bumps (“sleeping police”, for those of you in the UK) every few miles. One minute you’re flying along in hyper-productivity mode, and the next you’re inching along – digging through reference docs and forums trying to figure out why some little feature isn’t working.

My most recent speed bump is one that’s likely to appear in front of just about every Grails developer – getting pagination working with a result set generated by CriteriaBuilder – so I figured I’d help everyone join me in putting it in the rearview mirror.

The app we’re building (like most apps) contains domain instances in significant enough numbers that the standard Grails Criteria.list() feature isn’t practical. So we are replacing the .list pages (and supporting controller closures) searchable list forms that allow users to quickly locate objects and view or edit them.

Our search forms are flexible – they give the user the ability to enter data into any of several provided search fields, and will dynamically query for domain instances using the supplied criteria. Nothing special about them – I’ve been doing this sort of thing since before PowerBuilder came out. (Yeah, I’m that old.) So the basic search functionality was pretty easy. Getting pagination to work on these forms, however, turned out to be one serious set of speed bumps.

The first such form I implemented required some relational trickery that forced me to write a query in HQL. Well, two queries, actually – one to get the results, and another to get a count of those results. While this wasn’t hyper-productive, it wasn’t that bad. Moving along at 40 mph or so.

It was actually another search form, with a much simpler query implementation (using CriteriaBuilder) that introduced the speed bumps, again having to do with pagination.

Pagination in Grails, using the <g:paginate> tag, might seem at first to be pretty magical. The minute it doesn’t work for you, though, you quickly do enough research to realize there’s no magic. The tag sends and receives specific parameters that tell GORM list methods how many instances to bring back (maxsize), what set of instances to pick out from the result set (offset), and the overall size of the result set. If any of these three parameters isn’t set properly, you get some pretty strange behavior (pages in the UI with no contents, missing page traversal links, etc.).

I can say this, of course, because I was getting some of these strange behaviors when I first wrote the service method that implemented my dynamic query. The problem turned out to be some inaccurate information I had acquired about how CriteriaBuilder and the list() method work, coupled with sketchy documentation online regarding the specifics of the API.

Here’s the secret sauce. There are two signatures of the list() method on Criteria. Somewhat unexpectedly, each signature returns a different type of result set object. The default, no-arg signature returns a good old java.util.ArrayList. The other signature, one that takes pagination parameters (max and offset), returns an instance of grails.orm.PagedResultSet, which provides a handy (and essential) totalCount() method.

It all seems so obvious, right? The problem is the free supply of misinformation available to Grails developers. I found no fewer than three examples that showed how to pass max and offset values to Hibernate through code, within the accompanying closure, as in:

criteria.list {
maxResults(Integer.parseInt(params.max.toString()))
firstResult(Integer.parseInt(params.offset.toString()))
order(params.sort, params.order)
}

This works – sort of You do get a page of values back rather than a full result set. But you get those results in the form of a plain old ArrayList – not a PagedResultSet. And, of course, Groovy being a dynamically-typed language, your friendly compiler won’t let you know that the return type isn’t what you might have been expecting. What you do see, at runtime, is that when you call results.totalCount() to feed the result set size to your pagination tag, you get a “method does not exist” error. (Good think the creator of PagedResultSet didn’t choose to overload size(), or this bug would have been really difficult to find!

So there you have it, a little slice of Grails life in the speed bumps. Now that I’ve filed this information away, the road looks pretty clear ahead. At least for the next few hours.

Stay groovy, my friends…

2 thoughts on “Grails Pagination and CriteriaBuilder

  1. Hello, I saw an implementation, where the total count is part of the result .. eg

    def customers = Customer.createCriteria().list(max:maxRows, offset:rowOffset) {

    // first name case insensitive where the field begins with the search term
    if (params.firstName)
    ilike(‘firstName’,params.firstName + ‘%’)
    …… etc…
    }
    def totalRows = customers.totalCount

Leave a Reply

Your email address will not be published. Required fields are marked *