Combine IS NULL and :value in Doctrine 2 DQL

Question

Since other (old) questions didn't get proper answers, I'll try it again:

I regularly come across a scenario, where I want to query an entity with a specific value:

$query = $em->createQuery('SELECT e FROM Entity e WHERE e.parent = :parent');
$query->setParameter('parent', $parent);

Often, this value can be NULL, but WHERE e.parent = NULL yields no results, forcing me to hack around like this:

if ($parent === null) {
    $query = $em->createQuery('SELECT e FROM Entity e WHERE e.parent IS NULL');
}
else {
    $query = $em->createQuery('SELECT e FROM Entity e WHERE e.parent = :parent');
    $query->setParameter('parent', $parent);      
}

While I understand the rationale behind NULL != NULL in SQL / DQL, the fact is, the consequence is really annoying in this case.

Also, an example given in an older question doesn't work in DQL, for NULL != NULL.

->setParameter('parent', (is_null($parent) ? "NULL" : $parent));

I also tried this way, what someone kindly offered, but this will give a NonUniqueResult exception, because when parent is 1 for example, it'll give a double result.

SELECT e 
FROM Entity e 
WHERE (e.parent = :parent OR e.parent IS NULL)

Is there a cleaner way to perform this query, when the parameter can be null?

Answer 1

If you are not sure regarding your parameter value then you can rewrite your where clause as

SELECT e 
FROM Entity e 
WHERE (e.parent = :parent OR e.parent IS NULL)

If you have further filters for your query then make sure to use () around your OR criteria like

SELECT e 
FROM Entity e 
WHERE (e.parent = :parent OR e.parent IS NULL)
AND e.some = :some...