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?

Georgetown

Is my answer not correct? – Invert

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

Invert

Answer 2

You can use query builder:

$em = \Zend_Registry::get('em');

$qb_1 = $em->createQueryBuilder();

$q_1 = $qb_1->select('usr')
->from( '\Entities\user', 'usr' )
->where( 'usr.deleted_at IS NULL' )
->orWhere( 'usr.status='.self::USER_STATUS_ACTIVE )
->andWhere('usr.account_closed_on is null');

$q_1->getQuery()->getResult();

Cirenaica

I think you misunderstood the question! – Georgetown

Answer 3

Was just trying to solve the same issue and I actually found a solution for PostgreSQL.

$sql = 'SELECT * FROM company WHERE COALESCE(:company_id, NULL) ISNULL OR id = :company_id;'
$connection->executeQuery($sql, ['company_id' => $id]);

Based on given $id it returns the particular company or all, if null is passed. The problem seems to be that the parser does not know what you are actually going to do with the argument, so by passing it in to COALESCE function it knows that it is passing it into a function, so problem solved.

So if it started working in the "pure" SQL solution, using it inside the DQL should not be a problem. I didn't try that but Doctrine should have COALESCE support so the DQL should be easy to update.

Arlenarlena