GORM查询的麻烦与子句和外国键/属于子句

Question

Groovy版本2.4.8Grails版本2.5.1

我正在尝试使用类似子句从顾问表中拉出行，并且如果有一个公司名称传递到该方法中，我只想从该公司中拉出顾问。

我构建了两个没有公司组件的查询一个，但是当我删除设置公司以测试第二个查询的行时，我得到以下异常

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
}

Answer 1

您应该在同一地图中设置两个参数，例如：

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