Trouble with GORM query with like clause and foreign-key/belongs-to clause

Question

Groovy version 2.4.8 Grails version 2.5.1

I am attempting to pull rows from my Advisor table using a like clause and also if there is a Firm name passed into the method then I would like to only pull advisors from that Firm.

I constructed the two queries one without the Firm component which works fine but when I uncomment the line that sets the firm in order to test the second query runs and I get the below exception

org.springframework.orm.hibernate4.HibernateQueryException: Not all named parameters have been set: [firm] [from Advisor a where lower(a.firstName) like:keystrokes OR lower(a.lastName) like:keystrokes AND a.firm.name = :firm];

Code:

def getAdvisorsForKeystrokes(String keystrokes, String firm, int maxResults) {
    List<Advisor> advisors;
    firm = "Test Firm Name"
    if(firm.allWhitespace) {
        advisors = Advisor.findAll('from Advisor a where lower(a.firstName) like:keystrokes OR lower(a.lastName) like:keystrokes', [keystrokes:keystrokes + '%'], [max:maxResults])
    } else {
        advisors = Advisor.findAll('from Advisor a where lower(a.firstName) like:keystrokes OR lower(a.lastName) like:keystrokes AND a.firm.name = :firm', [keystrokes:keystrokes + '%'], [firm:firm], [max:maxResults])
    }

    return advisors
}

Classes:

class Advisor {
    String firstName
    String lastName
    String fullName
    String city
    String state
    Firm firm
    static belongsTo = [Case, Firm]
    static hasMany = [cases:Case]
    static constraints = {
    }
}


class Firm {
    String name
    static constraints = {
    }
}

If any one has any ideas of what the problem is or a good solution that would be amazing, thanks!

Edit:

I know it can rewrite like below and work but I tried many different ways to do it in one query and it is bothersome that I have not been able to find a way to get it to work.

def getAdvisorsForKeystrokes(String keystrokes, String firm, int maxResults) {
    List<Advisor> advisors;
    advisors = Advisor.findAll('from Advisor a where lower(a.firstName) like:keystrokes OR lower(a.lastName) like:keystrokes', [keystrokes:keystrokes + '%'], [max:maxResults])
    if(!firm.allWhitespace) {
        def firmModel = Firm.findByName(firm)
        advisors = advisors.findAll{ adv ->
            adv.firm == firmModel
        }
    }

    return advisors
}

Answer 1

You should set both params in the same map, like this:

advisors = Advisor.findAll('from Advisor a where lower(a.firstName) like:keystrokes OR lower(a.lastName) like:keystrokes AND a.firm.name = :firm', [keystrokes:keystrokes + '%', firm:firm], [max:maxResults])