postgresql - 带有like子句和foreign-key/belongs-to子句的GORM查询出现问题

Groovy版本2.4.8
Grails 2.5.1版

我试图使用like子句从Advisor表中提取行，并且如果该方法中传递了Firm名称，那么我只想从该Firm中提取Advisor。

我构造了两个查询，但没有Firming组件，该组件工作正常，但是当我取消注释设置Firmware的行以测试第二个查询运行时，出现以下异常
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];
码:

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
}

类:

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 = {
    }
}

如果有人对问题出在哪里有什么想法，或者对解决方案感到惊奇，那就谢谢!

编辑:

我知道它可以像下面这样重写并工作，但是我尝试了许多不同的方式在一个查询中执行此操作，但令我感到困扰的是，我一直无法找到一种使其工作的方式。

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
}

最佳答案

您应该在同一 map 中设置两个参数，如下所示:

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