Doctrine querybuilder/DQL to check if an m2m collection on entity is a subset of an array

Question

I have a Page entity which has a many-to-many relationship with Permission entity.

I have also have a User entity which also has a many-to-many relationship with Permission entity.

Using Doctrine's Querybuilder or DQL I want to get all Pages where the collection of Permissions for that Page is a subset of one User's permissions. How can I do this?

I've used Querybuilder's expr()->in and DQL's MEMBER OF but these only work for "one in many".

Answer 1

The code of your entities would have help in getting the properties name right, but, here is what you could do in DQL:

SELECT page 
FROM Page page 
INNER JOIN page.permissions permission
INNER JOIN permission.user user
WHERE IDENTITY(user) = :userId

Because of the two inner joins you will only get the pages that have permissions associated to the user specified in the WHERE clause. You could also do it with an additional join condition instead of the where clause. They'll lead to the same result:

SELECT page 
FROM Page page 
INNER JOIN page.permissions permission
INNER JOIN permission.user user WITH IDENTITY(user) = :userId