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

Question

I regularly come across a script where I want to request an object 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 does not produce any results, forcing me to crack 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); } 

Although I understand the rationale for NULL != NULL in SQL / DQL, the fact is that the consequence in this case is very annoying.

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

Answer 1

This is currently not possible. (I tried several ways for myself).

bindValue() with null only works to bind an INSERT / UPDATE value.

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

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

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

Unfortunately, the doctrine does not have this statement: http://doctrine1-formerly-known-as-doctrine.readthedocs.org/en/latest/en/manual/dql-doctrine-query-language.html#operators-and-operator -precedence

Answer 2

You can use the 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(); 

Answer 3

Just tried to solve the same problem, and I really 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 the given $id it returns a specific company or all if null passed. The problem is that the parser does not know what you are actually going to do with the argument, therefore, passing it to the COALESCE function, it knows that it passes it to the function, so the problem is solved.

So, if he started working in a “clean” SQL solution, using it inside DQL should not be a problem. I have not tried it, but Doctrine should have COALESCE support, so DQL should be easily updated.