Doctrine2 Soft Deletable on Many-to-Many

Question

I have a many-to-many connection User -> Address and use soft deletable plugin.

I need to write a query to find all users which do not have addresses linked with them. Without using soft delete the following works just fine:

$qb = $this->getRepository()->createQueryBuilder('users');
$qb->andWhere('users.addresses IS EMPTY');

Now, if softdeletable is enabled for Address and I remove all addresses for user, it will still return this user in the result. This is happening because the above query converts to the following SQL (simplified):

SELECT * FROM users u WHERE 
   (SELECT COUNT(*) FROM user_address ua WHERE ua.user_id = u.user_id) = 0)

As you can see, it is not joining Address entity and thus has no clue about deletedAt column on it.

So my question if how do I rewrite my DQL query to make it aware of deletedAt and filter out such records.

Thanks!

Answer 1

Solved it with a subquery like this:

$qb = $this->getRepository()->createQueryBuilder('u'); //Select Users

$subquery = '
    SELECT DISTINCT 1 FROM Address AS a2
    JOIN a2.users AS u2
    WHERE u2=u
';
$qb->andWhere('NOT EXISTS(' . $subquery . ')');