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

Question

Using Symfony and Doctrine with the DQL query builder, I need to optionally add some WHERE conditions with parameters, with some if condition 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: number of bound variables does not match number of tokens

Too few parameters: the query defines X parameters but you only bound Y

Too many parameters: the query defines X parameters and you bound Y

Expunge

in the where conditions set out of the if statement how do you pass the field value? probably you override the first setparameter. – Psychosocial

that was just an example, see my answer below – Expunge

Answer 1

the cleanest solution to this problem I've found so far, is to wrap all the parameters inside an array and then calling only once the setParameters() method, checking if there is at least one parameter to set:

$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);
}

Expunge

Your original code looks like it should work. I do it all the time except I use setParameter instead of setParameters. Might try using individual setParameter calls. It's a shame to have to break up the organization. – Stefaniestefano

Answer 2

You can set parameters one by one:

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

This way you'll be sure that you don't override other params with setParameters.

Decompose

I think it's better solution than the accepted. – Gazette