Geek Answers Handbook

Combine IS NULL and: value in Doctrine 2 DQL

Since the other (old) questions did not get the correct answers, I will try again:

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 = NULLdoes 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 effect in this case is really annoying.

Also, the example provided in an older question does not work in DQL, for NULL! = NULL.

->setParameter('parent', (is_null($parent) ? "NULL" : $parent));

I also tried so that someone kindly suggested, but that would throw a NonUniqueResult exception, because when the parent is 1, for example, it will give a double result.

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

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

+4
2

, where

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

, , () OR,

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

, ( ), DQL :

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

SQL "parent IS NULL", $parent - null ( "parent = ?" +).

:parent, NonUniqueResult :

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

( "" ):

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

"NULL!= NULL SQL/DQL":

, "NULL = NULL" "NULL!= NULL" , : NULL.
NULL "", "SELECT e FROM Entity e WHERE e.parent = NULL"
"SELECT e FROM Entity e WHERE e.parent != NULL"
( ),
NULL "" ( , "undefined" ), : "NOT (NULL)" - NULL ( TRUE), < > "SELECT e FROM Entity e WHERE NOT (e.parent = NULL)"
"SELECT e FROM Entity e WHERE NOT (e.parent != NULL)"
! ", " x IS NULL "" x IS NOT NULL "(" NOT (x IS NULL)") COALESCE(), , ISNULL(), IFNULL(), NVL() ...
(: , "" , , "(, NULL) (, TRUE)"
"(, NULL) AND (, FALSE)"
"- " , " " .)

0

More articles:


All Articles