Grails joinTable query issue

Question

I have a User domain and a Role domain and a working joinTable coded on the User side as

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

}

I am trying to query the database to pull all users with a security officer role with

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

But I am getting the error

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

What am I doing wrong?

score 1 · Accepted Answer · answered Oct 16 '15 at 17:30

1

I figured it out with a bunch of guess and checking.

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

answered Oct 16 '15 at 17:30

mjswartz

715
1
6
19

1

accept your answer as the correct answer to remove this question as unanswered :-) – Neoryder Oct 17 '15 at 04:27
Had the same problem here. Your solution worked. :) – Eric Pierce Dec 15 '15 at 19:34

score 0 · Answer 2 · answered Oct 16 '15 at 16:46

0

Roles is a hasMany relationship so I think following should work.

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

answered Oct 16 '15 at 16:46

Uday

619
3
7

score 0 · Answer 3 · answered Oct 16 '15 at 16:52

0

User has many roles, so in query you can't use u.roles = roleInstance.Try to use in [list of roles] or you can try the following query: def secList = User.findAll("from User as u where u.roles in (from Role r where r.name=:roleInstance)", [roleInstance:roleInstance])

answered Oct 16 '15 at 16:52

Karen Gharibyan

1
1
1

I am getting a new error Class: java.lang.ClassCastException // Message: Role cannot be cast to java.lang.String any ideas? – mjswartz Oct 16 '15 at 17:13
Replace [roleInstance:roleInstance] with [roleInstance:"security_officer"] – Karen Gharibyan Oct 16 '15 at 18:18

Grails joinTable query issue

3 Answers3