Pagination and Sorting in a Grails Application

Here is a new and very refreshing Technology called Groovy on Grails. Groovy is the language that is used and Grails is the framework for use in developing Web Based Applications widely.

Normally Web development companies prefer using more of PHP rather than other languages. But in case of Grails and Groovy there is use of Java and best part about it is that, the application is continuously running and objects are maintained from the time the application was first launched on the Web Server, unlike PHP where objects are created only when there is a request to the Server made and on getting a successfull response the object dies off. 

While querying to the database, either GORM or HQL queries are used. In GORM we first create a criteria, which does a ‘select *’on the table, and then narrow the criteria using where clause.

Example of a GORM queries:

def c = Customer.createCriteria()

def result = c.list{

projections{

groupProperty(‘date’)

groupProperty('place')

sum('total','total')

maxResults(10)

}

and{

eq(district,’North’)

       }

}

The result of this query i.e. the list is passed to the view, where the values are displayed accordingly.

The problem with this approach is that, when you use projections, you cannot use the params returned by the view for pagination and sorting. In case of simple GORM queries that do not use projections, params can be used. Also, if the ‘sortableColumn’ property of GRAILS tables is used, then the additional params (if any) need to be specified in the tag.

To make pagination and sorting work we can use HQL queries. Similar to the above query, an HQL query can be written as:

def result  = Customer.executeQuery("select c.date, c.place, sum(c.total) from Customer as c"+

" where c.district = :dist group by g.date, g.place order by g.$params.sort $params.order",

   [dist : district, params.max : 10, offset : params.offset])

Here, dist is used as a named parameter. Named parameters do not work for ‘order by’. In that case, we directly use the param value prefixed with ‘$’ symbol.

Another thing that needs to be taken care of here is that, when the page is loaded without passing the pagination, sort, order and offset parameters, the param values will be set to null. Hence before executing the query, the params should be checked for null values. If null, they should be set to a default value.