Problem with Grails Query Association

Question

I am having trouble recording a request 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 find all the persons belonging to the list of groups (let them say that the identifiers of the groups (1,2). This trick is that the person should be a member of both groups. I would prefer a query of criteria, but HQL is also ok.

Please note that the query to something group.id in (1,2)will not work, because it can be any of the groups, rather than and .

Answer 1

What is 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) ])

Greetings

Answer 2

, . Person . , () . - .

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

, , , .

Groovy

Answer 3

. , - , , , 2. , , .

Grails - http://www.nabble.com/has-many-through-relationship-query---GORM--td23438096.html

, , " Grails GORM GSQL".

@chadsmall

Answer 4

Here's another approach that avoids programmatically adding subqueries to your WHERE clause:

Query:

SELECT count(person.id) AS numPeople, person 
FROM Person as person 
INNER JOIN 
person.memberships AS mships
WITH mships.group.id IN (:groupIds) 
GROUP BY person.id 
HAVING COUNT(person.id) = (:numOfGroupIds)

And some sample values:

[
  groupIds: [8,9,439,86843]
  numOfGroupIds: 4
]

Part of this query before GROUP BY captures all the people that match any of the groups. Then, grouping by person and checking the number of results is equal to the number of groups in the list, you can check that this person is a member of all these groups.