Criteria using belongsTo field return empty result

Question

I have two controllers: timesheetcontroller and consultant controller. In Timesheet I have field:

class Timesheet {

...
String orderNumber
String groupCode

static belongsTo = [consultant: Consultant] //here
...

And in Consultant:

class Consultant {
...
    String userName
...

How can I build a query, which will filter list of timesheet by the name of consultant ? I'm trying something like this below, but it doesn't work.

Getting a name of consultant:

<input type="text" id="consultant" name="consultant" value="${flash.consultant}"/>

And in TimesheetController:

def list = {
    flash.consultant = params.consultant //here
    flash.groupCode = params.groupCode
    flash.orderNumber = params.orderNumber

    if(!params.max) {
        params.max = 20
    }
    def query
    def criteria = Timesheet.createCriteria()
    def results

    query = {
        and{
            like("userName", '%' + params.consultant + '%') //here
            like("groupCode", '%' + params.groupCode + '%')
            like("orderNumber", '%' + params.orderNumber + '%')
        }
    }

    results = criteria.list(params, query) 

    render(view:'index', model:[ timesheetInstanceList: results ])

}

Any idea?

Answer 1

Edited: You can use findAll method and reefer to consultant in HQL.

results = Timesheet.findAll("from Timesheet timesheet where timesheet.consultant.userName like :userName and timesheet.orderNumber like :orderNumber and timesheet.groupCode:groupCode",
                [userName: "%${params.consultant}%",orderNumber:"%${params.orderNumber}%",groupCode:"%${params.groupCode}%"],
                [max: params.max,offset:params.offset])