Skip to main content

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)
}

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

Post a Comment

Popular posts from this blog

Grails - cross-field Date validation

Often we run into domain classes with date fields. If the domain class has two datefields, startDate and endDate, and the rule for a valid combination is "endDate to be greater than startDate", how do we handle? I listed below two of the options, either using domain level constraints or using the domain classes. Option 1: Using domain constraints. Let us take a sample Grails Domain class class Student{ String name String school Date startDate Date endDate } Add the following constraints to enforce validation rules where name, school, startDate cannot be blank and endDate if present should be greater than startDate.

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:sortableColum