Doctrine2 QueryBuilder NOT IN statement not working

Question

Having some problem with doctrine query builder and NOT IN statements

I have table with four columns and some data:

id | directory_id| user_id | role_id

1    1              10     null
2    1              5      null
3    1              null   1
4    1              7      null

I want to select all fields which are not within two given array of ids.

$userIds = [10, 5];
$roleIds = [1];

My query:

$this->createQueryBuilder('da')
     ->where('da.directory = :directory')
     ->andWhere('da.user NOT IN (:userIds)')
     ->andWhere('da.role NOT IN (:roleIds)')
     ->setParameters([
         'directory' => $directory,
         'userIds' => $userIds,
         'roleIds' => $roleIds,
      ])
      ->getQuery()
      ->getResult();

As i understand it should return me the last row (#id 4) but it didn't.

Maybe someone knows whats wrong ?

Answer 1

Unfortunately comparisons with NULL values always result in UNKNOWN which is a falsy value.
The statement that has been executed on your fourth row is the following:

(1 <> 10 AND 1 <> 5) AND (NULL <> 1)

That results in the following and is a falsy result

(TRUE AND TRUE) AND (UNKNOWN)

To test NULL values you have to use IS NULL or IS NOT NULL. The following query should work:

$this->createQueryBuilder('da')
    ->where('da.directory = :directory')
    ->andWhere('(da.user NOT IN (:userIds) OR da.user IS NULL)')
    ->andWhere('(da.role NOT IN (:roleIds) OR da.role IS NULL)')
    ->setParameters([
        'directory' => $directory,
        'userIds' => $userIds,
        'roleIds' => $roleIds,
    ])
    ->getQuery()
    ->getResult();