php - Symfony 和 Doctrine DQL 查询生成器 : how to use multiple setParameters inside if condition checks?

Question

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

伪代码示例:

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

出现如下错误:

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

Answer 1

到目前为止，我发现这个问题的最简洁的解决方案是将所有参数包装在一个数组中，然后只调用一次 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);
}