Hi, guys, today we’re gonna talk about the really interesting and tricky Grails task, implementation of the custom sorting. It’s a pretty often appearing situation, when you need to sort the list of items based on the values that can only be calculated, and that are not persisted in the DB. I know what you might say, “Hey, why don’t you just use transient properties for that?”
Yep, a lot of you know that transient properties don’t have their values persisted in the database, though we may use them in the code like this: a.sortField. Let’s take a look at the example: if we write the following code in the domain class, ‘sortField’ here will be treated like a transient property.
Note: All the following suggestions and the code have been tested for Grails 1.3.7 and MySQL 5.1
class A {
def getSortField() {
//some complex db request goes here
}
}Note that I didn’t write the method as a closure, like this:
def getSortField = { }In this case, ‘sortField’ wouldn’t be treated like a property. Also, notice I didn’t specify the method’s return type. If we code this:
Integer getSortField() { }An exception will occur saying “org.hibernate.PropertyNotFoundException: Could not find a setter for property sortField”. To be able to use the above code, you should specify ‘sortField’ as the transient in the domain class:
static transients = ['sortField']So now that we have the domain class set up, can we get the sorted list with the following code?
A.findAll([sort: 'sortField', order: 'asc'])No, the above code will throw an exception: “org.hibernate.QueryException: could not resolve property: sortField”.
It happens because Hibernate translates the request to be processed with the database, and the database simply doesn’t know what the sortField is anyway.
There are two solutions in order to implement this task I’ve found so far.
Using Groovy sorting could be a really nice solution. Check this code out:
/**
* Grails action for processing the custom sorting, params to accept:
* field - property to sort by(could be transient)
* order - order to sort, could be ascending or descending
*/
def customSorting = {
def listToSort = A.findAll()
if (params.order == 'asc') {
listToSort.sort{it.getProperty(params.field)}
} else if (params.order == 'desc') {
listToSort.sort{-it.getProperty(params.field)
}
[list: listToSort]
}
As you can see, Groovy sorting syntax is pretty self-explanatory. So what you want to remember, except that, is a.sort{it.b} stands for the ascending order, and a.sort{-it.b} for the descending. There is also a nice and elegant solution provided by Grails: derived properties. Derived properties take their values from the SQL expression, so the database can calculate them and sort request results by them. Note that the formula for the derived properties should be based on the field/table names as they are expressed in your database, not in your domain model. Let’s get more detailed with the following example. Here, some companies have collections of tickets for some events they offer, and tickets have a collection of ticket types – product order lines. The task is to sort companies by the minimum cost of the tickets they offer.
class ProcutOrderLine {
BigDecimal price
belongsTo = [ticket: Ticket]
}
class Ticket {
hasMany = [productOrderLines: ProductOrderLine]
belongsTo = [parent: User]
}
class Company {
BigDecimal price
static hasMany = [tickets: Ticket]
static mapping = {
price formula: "(select min(x.price) from product_order_line x " +
"WHERE x.ticket_id IN(select x.id from ticket x " +
"WHERE x.parent_id = id))"
}
}Note that the whole request is held inside parentheses; it won’t work without them. Also, even if the request works in the DB console, it may not work for your code, so make sure you include all those ‘x’ into your formula.
So after you get things to work, the following code would be a solution:
/**
* Grails action for processing the custom sorting, params to accept:
* field - property to sort by(could be transient)
* order - order to sort, could be ascending or descending
*/
def customSorting = {
def listToSort
if (params.order == 'asc' || params.order == 'desc') {
listToSort = Company.findAll([sort: params.field, order: params.order])
}
[list: listToSort]
}
Another pretty self-explanatory code, isn’t it? Using derived properties also has an awesome advantage: you may use them for all the dynamic methods that Grails provides, like findBy. So, for the domain example we considered, you may find something like this useful:
Company.findByPriceLessThan(50)Hope you have found this article interesting and helpful, so have fun!
