CODEWITHSUNDEEP
symfonydoctrine
ZugZwang
ZugZwang

Reputation: 418

Doctrine - select a NULL value from db

I have a class, let's say Test.php. This class has an id, a description, a code, and a discount percentage.

This code returns an object:

$test = $this->getDoctrine()->getRepository('\UserBundle\Entity\Test')->findOneBy(array('code' => 12345 ));

This code, instead, doesn't return an object:

$test = $this->getDoctrine()->getRepository('\UserBundle\Entity\Test')->findOneBy(array('code' => null ));

The code field is NULL by default.

Any suggestion? Thanks!

Upvotes: 2

Views: 8046

Answers (3)

Stevan Tosic
Stevan Tosic

Reputation: 7199

You just can use doctrine expressions in TestRepository class

$db = $this->createQueryBuilder('t');

return $db->select('t')
    ->where($db->expr()->isNull('t.field1'))
    ->orWhere($db->expr()->isNull('t.field2'))
    ->orWhere($db->expr()->isNull('t.field3'))
    ->getQuery()
    ->getResult();

Upvotes: 0

user7426410
user7426410

Reputation: 71

While qooplmao is correct for the first part "To query for a null value you need to use is null rather than = null", I have to say that the second part "which the Doctrine shortcut don't do" is not true (at least for version 2.5.4, which was the latest stable release at the time of the question).

\Doctrine\ORM\EntityRepository::findOneBy() (with argument array('code' => null)) ultimately calls
\Doctrine\ORM\Persisters\Entity\BasicEntityPersister::getSelectConditionStatementSQL() (with arguments 'code', null, null), which does handle the special case of a null value (shortened to the essential here, $columns is an array with a single element e.g. array('t0.code'), $value is null):

        foreach ($columns as $column) {
            $placeholder = '?';

            ...

            if (null === $value) {
                $selectedColumns[] = sprintf('%s IS NULL', $column);
                continue;
            }

            $selectedColumns[] = sprintf('%s = %s', $column, $placeholder);
        }

and then executes a SQL query with a correct condition, of the form:

SELECT t0.id AS id_1, t0.description AS description_2, t0.code AS code_3, ...
FROM Test t0
WHERE t0.code IS NULL
LIMIT 1

(It even handles null in an array value, e.g. with argument array('code' => array(12345, null, 42)), it will generate a SQL condition of the form

WHERE (t0.code IN (12345, NULL, 42) OR t0.code IS NULL)

(actually it could remove the NULL from the IN, but the result is the same).)

Edit: That has been the case since version 2.5.0 for both simple and array values (thanks to commit 733102b4a109c0bb8ca026b1dbbadaa9bb62ae70), and even since version 2.1.0 for simple (non-array) values (like your case) (thanks to commit a3290075260cdafdd17952ca14daa305fabccfe2).

So the fact that your findOneBy(array('code' => null )) didn't return an object can only mean that your table just didn't have any row with column code set to NULL, which seems confirmed by your discussion with qooplmao in the comments of their answer, and so if you retry now with your new data, it should return an object the same as with a QueryBuilder or DQL.

Upvotes: 2

qooplmao
qooplmao

Reputation: 17759

Taken from this answer

In SQL, a comparison between a null value and any other value (including another null) a using a logical operator (eg =, !=, <, etc) will result in a null, which is considered as false for the purposes of a where clause. The reasoning is that a null means "unknown", so the result of any comparison to a null is also "unknown".

To query for a null value you need to use is null rather than = null which the Doctrine shortcut don't do. You would need to do this by using SQL/DQL directly or the query builder like...

/** QueryBuilder */
$test = $this
    ->getDoctrine()
    ->getRepository('\UserBundle\Entity\Test')
    ->createQueryBuilder('t')
    ->where('t.code is null')
    ->getQuery()
    ->getResult();
/**
 * Alternatively, if trying to find a single record you can use ->getOneOrNullResult();
 * as ->getResult() will throw a NoResultException if no results are found
 */

/** DQL */
$test = $this
    ->getDoctrine()
    ->createQuery('
        SELECT t
        FROM \UserBundle\Entity\Test t
        WHERE t.code is null
    ')
    ->getResult()
    ->getOneOrNullResult();

Upvotes: 4

Related Questions