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...

Answer 2

If your scenario is really that simple and you just want to get the entities (and don't really care about the query), then instead of DQL you can use a repository function:

$entities = $em->getRepository('Entity')->findBy(array('parent' => $parent));

which will automatically special-case the SQL condition as "parent IS NULL" if $parent is null (else the basic condition "parent = ?" + parameter).

Otherwise, add a condition on :parent to avoid the NonUniqueResult exception in your combined query:

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

or even (directly translating from your "hack"):

WHERE ((:parent IS NULL AND e.parent IS NULL) OR (:parent IS NOT NULL AND e.parent = :parent))

---

Side note about "NULL != NULL in SQL / DQL":

Strictly, both "NULL = NULL" and "NULL != NULL" aren't either TRUE nor FALSE: both return NULL.
Now, NULL isn't "truthy", so both queries
"SELECT e FROM Entity e WHERE e.parent = NULL" and
"SELECT e FROM Entity e WHERE e.parent != NULL"
won't ever return any row (for whatever data),
but NULL isn't "falsy" either (it is a third kind, say "undefined"), and negating it doesn't change that: "NOT (NULL)" is still NULL (and not TRUE), so
"SELECT e FROM Entity e WHERE NOT (e.parent = NULL)" and
"SELECT e FROM Entity e WHERE NOT (e.parent != NULL)"
won't ever return any row either!
Hence the need to use the operators "x IS NULL" and "x IS NOT NULL" (or "NOT (x IS NULL)") or COALESCE(), or vendor-specific functions like ISNULL(), IFNULL(), NVL(), etc.
(Remark: there can be cases where "undefinedness" is solved out automatically, e.g. in conditions
"(expression that yields NULL) OR (expression that evaluates to TRUE)" or
"(expression that yields NULL) AND (expression that evaluates to FALSE)"
because "anything OR TRUE" is always TRUE and "anything AND FALSE" is always FALSE.)