[FIXED] Doctrine QueryBuilder - ManyToMany - NOT IN - how do I filter only entities where relation does not contain ID?

Issue

I have an issue with constructing a suitable query:

$queryBuilder
           ->innerJoin(
               sprintf('o.%s', $property), 
               $property, 
               'WITH',
               sprintf('%s.id = :%s', $property, $parameterName)
           )
           ->setParameter($parameterName, $value)
           ->andWhere(sprintf('%s IS NULL', $property));

When I filter with ->andWhere(sprintf('%s IS NOT NULL', $property)), it's all good, I get all four correct entities.

However, on the ->andWhere(sprintf('%s IS NULL', $property)) query, I am getting some entities from the NOT NULL query, because there are other entities in the toMany relationship, which satisfy the IS NULL clause.

Is there any way to make a query exclusive (do not return a result when the IS NULL condition is triggered by ANY of the related entities).

I know this is quite confusing, but here is an example:

You have a party with invitees.

You want to filter all parties that DO NOT have that particular invitee (say, with id 1).

I can't do that when there are other invitees to the party. For example, one party has invitees with ids 1, 2, and 3. This one will get back to me even when it should not.

If it only had one invitee and this invitee was with id 1, all is fine.

I hope that makes sense.

This is the exact same issue as this one (which is pure sql):

Selecting with NOT IN and Many to Many relationship

Also, I am totally aware of the workaround using a subquery, but this is not feasible in my case, as I need it for a low level filter, where it's gonna be a mess, if I start querying other entities.

Solution

use MEMBER OF expression.

LIKE:

->andWhere(sprintf(':%s NOT MEMBER OF o.%s', $id, $toManyRelation))

Answered By - Ravi Damasiya