Saturday, May 14, 2011

Implementing advanced sort in Grails

The "list" pages generated by inbuilt scaffolding/template features of grails have pagination and sorting features. However, if the domain object displayed in the list is a nested object having another domain object as a property, you may notice that sort is not enabled for that field. Boiler plate code for the header of the list is shown below.
















As you would have noticed few columns have sortable columns automatically generated by Grails command, generate-all or generate-views.

The properties 'partyAccount' and 'bankAccount' in this sample are domain classes nested in the domain class 'partyTransaction'. We could convert them to sortable columns by using the tag g:sortableColumn.
eg:
<g:sortableColumn property="party" title="${message(code: 'paymentTransaction.party.label', default: 'Party')}"></g:sortableColumn>


The sort behavior is magically introduced after this modification.

The version of the code generated by grails in the corresponding controller class is shown below:



def list = {
params.max = Math.min( params.max ? params.max.toInteger() : 10, 100)
params.sort = params.sort?:'id'
[ paymentTransactionInstanceList: PaymentTransaction.list( params ),paymentTransactionInstanceTotal: PaymentTransaction.count() ]
}


The sort result may not be as anticipated. How do we enforce sort on a custom property?
An enhanced version to get custom sort behavior is given below:



def list = {

params.max = Math.min( params.max ? params.max.toInteger() : 10, 100)
params.offset = params.offset? params.offset.toInteger():0
params.sort = params.sort?:'id'
params.order = params.order?:'desc'
def lst = PaymentTransaction.createCriteria().list{

createAlias ('partyAccount', 'pa')

if (params.sort == 'partyAccount')
order ('pa.accountName', params.order)
else

order (params.sort, params.order)

maxResults (params.max)
firstResult (params.offset)
}

[ paymentTransactionInstanceList: lst, paymentTransactionInstanceTotal: PaymentTransaction.count() ]
}



We can also implement sort on more than one column as below:



def list = {

params.max = Math.min( params.max ? params.max.toInteger() : 10, 100)
params.offset = params.offset? params.offset.toInteger():0
params.sort = params.sort?:'id'
params.order = params.order?:'desc'
def lst = PaymentTransaction.createCriteria().list{

createAlias ('partyAccount', 'pa')
createAlias ('bankAccount', 'ba')

order ('pa.accountName', 'asc')
order ('ba.accountName', 'asc')

order (amount, 'desc')
maxResults (params.max)
firstResult (params.offset)
}

[ paymentTransactionInstanceList: lst, paymentTransactionInstanceTotal: PaymentTransaction.count() ]
}



Sample classes used in the example:

class PaymentTransaction{
static belongsTo =[partyAccount:Account, bankAccount:Account, ...]
double amount
..
..


}

class Account{
String accountName
..
..
}





Note: In case the property 'partyAccount' is nullable you may force LEFT JOIN to get the right results.


createAlias ('partyAccount', 'pa', CriteriaSpecification.LEFT_JOIN)

2 comments:

  1. Thanks a lot,

    saved hours....

    ReplyDelete
    Replies
    1. very Helpful.What if you have dynamically generated headers,How do you sort and paginate them.thanks

      Delete