Grails query association problem

Question

I'm having trouble writing a query for the following domain classes:

class Person {
  static hasMany = [memberships: Membership]
}

class Membership {

    static belongsTo = [person: Person, group: Group]

    Date joinDate = new Date();
    Group group;
    Person person;
}

class Group {
     static hasMany = [memberships: Membership]
}

Basically, I want to found all persons which belongs to a list of groups (Let's say group ids are (1,2). The trick here is that the person must be a member of both groups. I'd prefer a criteria query, but HQL is ok also.

Note that querying with something like group.id in (1,2) won't work because it can be any of the groups, not both.

Answer 1

That's my simple HQL approach:

Person.executeQuery("FROM Person x WHERE x IN (SELECT m.person from Membership m WHERE m.group = :group1) AND x IN (SELECT m.person from Membership m WHERE m.group = :group2)", [ group1: Group.get(1), group2: Group.get(2) ])

Cheers