DQL How to return all records when parameter is empty

Question

I am building a Symfony app where a user can make some search through a search form with many fields. Now my DQL query looks like this:

$users = $repository->createQueryBuilder('u')
      ->addSelect('u')
      ->from('AppBundle:User', 'b')
      ->where('u.Number = :Number **OR u.Number = :blank'**)
      ->andWhere('u.Code = :Code')
      ->setParameter('Number', $Number)
      ->setParameter('blank', $blank)
      ->setParameter('Code', $code)
      ->getQuery()
      ->getResult();

The problem is that I want my request to return ALL records related to the criteria when a user leaves some fields blank. Right now no records are returned because the system thinks I want entries with particular "blank" criteria. I would appreciate any ideas. Thank you

Answer 1

If I understand correctly, you actually have 2 parameters in this case, which are: Number and Code. And if one of them is "blank", then you have the blank variable set ?

If this is the case, I think you should write your request like this:

$query = $repository->createQueryBuilder('u')
        ->addSelect('u')
        ->from('AppBundle:User', 'b')

if ($Number != "")
  $query->where('u.Number = :Number')
        ->setParameter('Number', $Number);

if ($code != "") {
  if ($Number != "") $query->andWhere('u.Code = :Code');
  else $query->where('u.Code = :Code');
  $query->setParameter('Code', $code);
}

$users = $query->getQuery()->getResult();