Grails joinTable查询问题

Question

我有一个用户域，一个角色域和一个工作joinTable在用户端编码为

static hasMany = [ roles: Role ]
...
static mapping = {
    table 'user_data'
    id column: 'employee_number', name: 'employeeNumber', generator: 'assigned', type: 'int'
    version false
    sort 'lastName'
    roles joinTable: [ name: 'user_role' ]
}

我正在尝试查询数据库，以拉出具有安全员角色的所有用户

def roleInstance = Role.find { name == 'security_officer' }
def secList = User.findAll("from User as u where u.roles = :roleInstance", [roleInstance:roleInstance]) 

但是我得到了错误

Class: com.microsoft.sqlserver.jdbc.SQLServerException
Message: The value is not set for the parameter number 1.

我做错了什么?

Answer 1

我猜了一堆和检查。

 def roleInstance = Role.findByName("security_officer")
 def query = User.where { 
     roles { 
         id == roleInstance.id 
     } 
 }
 def securityOfficerList = query.list()

Answer 2

角色是一个hasMany关系，所以我认为下面应该工作。

def secList = User.findAll("from User as u where u.roles in (:roleInstance)", [roleInstance:[roleInstance]]) 

Answer 3

用户有多个角色，所以在查询中你不能使用u.r uroles = roleInstance。尝试在[list of roles]中使用，或者您可以尝试以下查询:def secList =用户。findAll("from User as u where .roles in (from Role r where .name=:roleInstance)"， [roleInstance:roleInstance])