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, basing 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, lot of you knows that transient properties doesn’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 will write the following code in the domain class, ‘sortField’ here will be treated like the transient property.

Note: All following suggestions and the code is tested for the
Grails 1.3.7 and MySQL 5.1

class A {
   def getSortField() {
       //some complex db request goes here
   }
}Code language: JavaScript (javascript)

Note that I didn’t write method as a closure, like this:

def getSortField = {  }

In this case ‘sortField’ wouldn’t be treated like a property. Also notice I didn’t specified method’s return type. If we will code this:

Integer getSortField() {  }Code language: CSS (css)

Exception will occur saying “org.hibernate.PropertyNotFoundException: Could not find a setter for property sortField”. To be able to use above code you should specify ‘sortField’ as the transient in the domain class:

static transients = ['sortField']Code language: JavaScript (javascript)

So now when we have domain class set up, can we get the sorted list with the following code?

A.findAll([sort: 'sortField', order: 'asc'])Code language: CSS (css)

No, the above code will throw an exception: “org.hibernate.QueryException: could not resolve property: sortField”.
It happens because the Hibernate translates the request to be processed with the database, and database simply doesn’t know what the sortField is anyway.
There’s 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]
}
Code language: PHP (php)

As you can see, Groovy sorting syntax is pretty self explainable. 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 takes its values from the SQL expression, so the database can calculate it and sort request results by it. Note that formula for the derived properties should be based on the field/table names as they 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 on some events they offer, and ticket has 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))"
   }
}Code language: PHP (php)

Note that the whole request is held inside parenthesis, it won’t work without them. Also even if the request works in the db console, it may not work for your code, so may sure you include all those ‘x’ into your formula.
So after you get things to work, 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]
}
Code language: PHP (php)

Another pretty self explainable code, isn’t it? Using derived properties also has awesome advantage: you may use it for all the dynamic methods that Grails providing, like findBy. So for the domain example we considered, you may find useful something like this:

Company.findByPriceLessThan(50)Code language: CSS (css)

Hope you have found this article interesting and helpful, so have fun!