CODEWITHSUNDEEP
symfonydql
Jack
Jack

Reputation: 931

DQL How to return all records when parameter is empty

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

Upvotes: 0

Views: 344

Answers (2)

Dric512
Dric512

Reputation: 3729

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();

Upvotes: 0

Tushar D
Tushar D

Reputation: 88

You can use Expr class in DQL to further structure query around 'blank' field submitted by user. Simple if(!empty($field)) will do the trick 

$query = $repository->createQueryBuilder('u')
  ->addSelect('u')
  ->from('AppBundle:User', 'b')
  ->where('u.Number = :Number')
  ->andWhere('u.Code = :Code')
  ->setParameter('Number', $Number)      
  ->setParameter('Code', $code);

if(!empty($blank)){
  $query->andWhere($query->expr()->orX(
     $query->expr()->eq('u.Number', ':blank')
  ));
  $query->setParameter('blank', $blank);
}

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

Upvotes: 1

Related Questions