I have two tables:
Users
Groups
One user can have many groups and one group can have many users.
I am trying to build a query where I have an array of groups and I want to find all users who belong to ALL the groups in array and print all the groups of each of those users. For example:
Table Users:
User 1 - belongs to groups 1,2,3
User 2 - belongs to groups 2,3,4,5
User 3 - belongs to groups 3,4
User 4 - belongs to groups 4,5
Table Groups:
Group 1
Group 2
Group 3
Group 4
If I have an array [Group 3, Group 4], I'd like to filter my tables and get: User 2 - Groups 2,3,4,5 User 3 - Groups 3,4
No matter what I try (using 'MEMBER OF', 'IN', etc.), I will always get all the users that have at least one of the groups (e.g. User 1 from my example which has Group 3 but not Group 4). Here's a relevant part of my code:
$qb = $this->createQueryBuilder('u')
->leftJoin('u.groups', 'g')
->where(':groups MEMBER OF u.groups')
->setParameter('groups', $groupsArray);
When I use "IN" instead of "MEMBER OF", the only difference is that I will get the same results but only groups from the Array will be in the data set (e.g. User 2 will have groups 3 and 4 instead of all of the ones from the example).
EDIT (answer to Destroy666):
$qb = $this->createQueryBuilder('u')
->addSelect('COUNT(DISTINCT(g.id)) as total_groups')
->leftJoin('u.groups', 'g') ->where('g.id IN (:groups)')
->setParameter('groups', $groupsArray) ->groupBy(u.id)
->andHaving('total_groups = ' . count($groupsArray));
This works but it doesn't fetch all the groups the user belongs to. Any ideas?
