Symfony - Doctrine - Repository - WHERE value might be null or not

Question

Sometimes a category will have a value and sometimes it will be null (this condition is omitted).

The following will not return a product if it has a null category.

What's the best way to resolve this without having to check if category is null and then use ->where('product.category is null')? I'm in a situation where I have 30 different fields where sometimes the value will be null and sometimes it won't.

$this->createQueryBuilder('product')
    ->andWhere('product.category = :category')
    ->setParameter('category', $category)
    ->getQuery()
    ->getOneOrNullResult()
;

I'm not 100% sure what you're looking for. Are you looking for a shorthand for `WHERE some_field = some_value OR some_field IS NULL`? — El_Vanja, Apr 26 '21 at 13:03
$category will sometimes have a value and sometimes it will be 'null'. If it is null then the query above won't find the product even though a product exists with a null category. What's the 'shortest' way to resolve this? — Rayman, Apr 26 '21 at 13:51
Ah, I see now, you need either equality or `is null`, based on what `$category` is. There is no simple, built-in functionality for this (or at least none that I know of), but you could always wrap this in a method. — El_Vanja, Apr 26 '21 at 14:05
Found an old question with the same problem I'm facing: https://stackoverflow.com/questions/28405835/combine-is-null-and-value-in-doctrine-2-dql — Rayman, Apr 26 '21 at 14:17

score 0 · Answer 1 · answered Apr 26 '21 at 18:47

You need to add check for null by your own. You can do it in the next way:

$qb = $this->createQueryBuilder('product');

if (null === $category) {
  $qb->andWhere('product.category IS NULL');
} else {
  $qb->andWhere('product.category = :category')
    ->setParameter('category', $category)
  ;
}

return $qb->getQuery()
  ->getOneOrNullResult()
;

Symfony - Doctrine - Repository - WHERE value might be null or not

1 Answers1