Symfony and Doctrine DQL query builder: how to use multiple setParameters inside if condition is checked?

Question

Using Symfony and Doctrine with the DQL query builder, I need to add some WHERE conditions with parameters, with some conditional checks.

Pseudo-code example:

$qb = $this->getEntityManager()->createQueryBuilder(); $qb = $qb ->select('SOMETHING') ->from('SOMEWHERE') ->where('SOME CONDITIONS'); if ( SOME CHECK ) { $qb ->andWhere('field1 = :field1') ->andWhere('field2 = :field2') ->setParameters([ 'field1' => $myFieldValue1, 'field2' => $myFieldValue2, ]); } else { $qb ->andWhere('field1 = :field1') ->setParameters([ 'field1' => $myOtherFieldValue1, ]); } 

Getting errors like:

Invalid parameter number: the number of related variables does not match the number of tokens

Too few parameters: the query defines the parameters of X, but you only bind Y

Too many parameters: the query defines the parameters of X, and you bind Y

Answer 1

The cleanest solution to this problem that I have found so far is to wrap all the parameters inside the array, and then call it only once with the setParameters() method, checking for at least one parameter:

 $qb = $this->getEntityManager()->createQueryBuilder(); $qb = $qb ->select('SOMETHING') ->from('SOMEWHERE') ->where('SOME CONDITIONS') $parameters = []; if ( SOME CHECK ) { $qb ->andWhere('field1 = :field1') ->andWhere('field2 = :field2'); $parameters['field1'] = $myFieldValue1; $parameters['field2'] = $myFieldValue2; } else { $qb->andWhere('field1 = :field1'); $parameters['field1'] = $myOtherFieldValue1; } if (count($parameters)) { $qb->setParameters($parameters); } 

Answer 2

You can set the parameters one at a time:

 $qb ->setParameter('field1', $value1) ->setParameter('field2', $value2); 

This way you will be sure that you do not override other parameters with setParameters .