Symfony和Doctrine DQL查询构建器:如果条件检查，如何在其中使用多个setParameters ?

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

使用Symfony和Doctrine与DQL查询构建器，我需要选择性地添加一些带参数的WHERE条件，以及一些if条件检查。

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

参数太少:查询定义了X参数，但只绑定了Y

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

太多的参数:查询定义了X参数，然后绑定了Y

2 个解决方案

#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:

到目前为止，对于这个问题，我找到的最干净的解决方案是将所有参数封装在一个数组中，然后只调用一次setParameters()方法，检查是否至少有一个参数需要设置:

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

#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.

这样，您就可以确保不会用setParameters覆盖其他参数。