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
1
2
3
4
5
class A {
def getSortField() {
//some complex db request goes here
}
}
Note that I didn’t write method as a closure, like this:
1
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:
1
Integer getSortField() { }
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:
1
static transients = ['sortField']
So now when we have domain class set up, can we get the sorted list with
the following code?
1
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 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:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
/**
* 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 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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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 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:
1
2
3
4
5
6
7
8
9
10
11
12
13
/**
* 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 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:
1
Company.findByPriceLessThan(50)
Hope you have found this article interesting and helpful, so have fun!