Tuesday, May 17, 2011

Grails - Querying complex associations

Criteria class allows performing complex searches on grails objects. There are number of shortcut methods for performing queries but these methods have limitations in terms of number of conditions used in "where clauses". Traditional sql "joins" are not possible as shown in some of the Grails "Finder" methods shown below.



Sample 1:
def list = AccountTransaction.findAllByCompanyCodeAndVoucherDateBetween(branch, fromDate, toDate, params)

Sample 2:
def list = AccountTransaction.findAllByCompanyCodeAndVoucherDateGreaterThanEquals(branch, fromDate, params)

Sample 3:
def list = AccountTransaction.findAllByCompanyCodeAndTransGroup(branch, group, params)




"params" contains attributes related to sorting, paging etc. It is very easy to use finder methods but when you want to filter objects by more conditions we need to look for alternatives.

For understanding the relationships used in this sample, I listed the grails domain classes.



class TransactionType{
String code
String description

}

class AccountTransactionGroup{
static belongsTo = [transactionType: TransactionType, ..]
Date createDate
..
..
}

class AccountTransaction{
static belongsTo = [transGroup: AccountTransactionGroup, ...]
Date voucherDate
String companyCode
..
..
}




The Criteria closure used in the example below will allow us to query transactions of the given transaction type , say, "Journal" or "Receipt" or "Payment". Note the usage of "createAlias" used to link the objects.
AccountTransaction is linked to AccountTransactionGroup.
AccountTransactionGroup is linked to TransactionType.
TransactionType has the property we are trying use in the query. ("code")

The variable 'varr' contains list of transaction codes, one or more. 'in' is used to specify this list in the closure.




def lst = AccountTransaction.createCriteria().list{
createAlias ('transGroup', 'tg')
createAlias('tg.paymentTransaction', 'tgp')
createAlias('tgp.transactionType', 'tgpt')
if (branch != null) eq('companyCode',branch)
if (toDate == null) eq('voucherDate', fromDate)
else between ('voucherDate', fromDate, toDate)
varr.size>0?'in' ('tgpt.code', varr):''
order (params.sort,params.order)
maxResults (params.max)
firstResult (params.offset)
}

3 comments:

  1. Can you post the generated sql from above criteria query? Curious to see the sql that you would have to have had written by hand.

    Thanks

    ReplyDelete
  2. Many thanks! solved my headache :)

    ReplyDelete