Grails query association problem

Question

Hi, 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

Answer 2

Maybe you don't need a query. In the Person class, memberships is a list of Membership objects. You can find if an object is in a Collection (list) without making any query. Something like this should do the job.

if(Person.memberships.contains(Membership.findByPersonAndGroup(person1,group1)) && Person.memberships.contains(Membership.findByPersonAndGroup(person1,group2))){
  ...do something...
}

Probably the other solution is simplier, but I think this is just another option.

Cheers

Groovy Collections

Answer 3

Interesting problem. Not sure the previous solutions are generic on the number of matched groups - in the cases so far, it's fixed at 2 I think. Although there is probably a way to make them variable.

Another way I describe here on the grails messageboard - http://www.nabble.com/has-many-through-relationship-query---GORM--td23438096.html

Including commentary from the author, Robert Fischer, of "Grails Persistence with GORM and GSQL".

@chadsmall