HQL issue with hasMany String

Question

That's the context of this issue:

I have a Grails class:

class User{
   long id
   static hasMany = [skills: String]
   ...
}

I'd like to get users from the db on 2 conditions:

• set of ids
• set of skills (strings)

I wrote this query that works for the ids, but I can't get the skill part working:

User.findAll( "from User 
where id in (5067120,5067121,...5067139)" )

For the moment I'm selecting the Users with the right skills manually after this query, but obviously it's not an efficient solution. How can I solve this?

Thanks!

Answer 1

This should work:

def ids = [5067120L, 5067121L, ...5067139L]
def skills = ['skill 1', 'skill 2']

def users = User.executeQuery(
   'select distinct u ' +
   'from User u inner join u.skills skills ' +
   'where u.id in (:ids) and skills in (:skills)',
[ids: ids, skills: skills])

Note that you don't need to specify the id field if it's a regular long, Grails does that for you.