Raffaello Raffaello - 7 months ago 24
SQL Question

Doctrine many to many relationship NOT IN [jointable]

I've 2 tables with many-to-many relationship => users, roles.

I need to find all users THAT AREN'T IN ANY ROLE.

In SQL it can be done:

SELECT *
FROM `user`
WHERE `id` NOT
IN (
SELECT `user_Id`
FROM user_role
)


user_role
is the joining table of many to many relationship from
user
and
role
.

How can obtain the same result with
DQL
?

UPDATE

The
user_role
table is an automated mapping from doctrine, if i try to use it, I get "entity not found"

Asking Better:

How can I do the same result with
CreateQueryBuilder
?

I can't be able to using the user_role table in my entity, because it's autogenerated with many-to-many relathionship annotation (or can I use it?).

Answer

This should work:

SELECT u
FROM user u
LEFT JOIN u.roles r
WHERE r IS NULL