Is there a simple way to combine IS NULL and = :value in Doctrine 2 DQL?

Question

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.

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

Answer 1

It's not possible at the moment. (Tried for myself just several ways).

bindValue() with null only works for INSERT/UPDATE value binding.

I think the limitation is in PDO or SQL Syntax itself and not Doctrine.

You can use the QueryBuilder, so you only need to "duplicate" the WHERE part, instead of the whole query: http://doctrine-dbal.readthedocs.org/en/latest/reference/query-builder.html#where-clause

EDIT: It's possible in native SQL: https://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_equal-to

Sadly doctrine does not have that operator: http://doctrine1-formerly-known-as-doctrine.readthedocs.org/en/latest/en/manual/dql-doctrine-query-language.html#operators-and-operator-precedence