如何使用查询生成器从子查询更新?

Question

我使用Symfony 5, Doctrine和PostgreSQL。

在一些事务存储库中，我试图运行下面的代码:

$queryBuilder = $this->em->createQueryBuilder('tr');
$queryBuilder2 = $this->em->createQueryBuilder('t');
$queryBuilder2->select('t.id', 't.status')
->where(
$queryBuilder->expr()->isNotNull('t.timeoutAt'),
$queryBuilder->expr()->lt('t.timeoutAt', ':timeoutAt'),
$queryBuilder->expr()->in('t.status', ':status')
)->setParameters([
'timeoutAt' => Carbon::now(TimezoneEnum::UTC),
'status' => ['pending', 'done'],
])->getDQL();

$queryBuilder->update()
->set(
't.status',
'CASE WHEN t.status = :statusPending THEN :statusDone ELSE :statusInProgress END'
)
->set('tr.updatedAt', ':updatedAt')
->from($queryBuilder2->getDQL(), 't')
->where('t.id = tr.id')
->setParameters([
'updatedAt' => Carbon::now(TimezoneEnum::UTC),
'statusPending' => 'pending',
'statusDone' => 'done',
'statusInProgress' => 'progress',
]);

调用$queryBuilder->getQuery()->getResult();后，我得到了一个错误:

DoctrineORMQueryQueryException:[语法错误]line 0, col 37: Error: Expected DoctrineORMQueryLexer::T_SET, got '，'

如果要删除->from($queryBuilder2->getDQL(), 't')错误将被修复，但我需要这个FROM。

$queryBuilder2->getQuery()->getResult()工作完美;

如何正确地把sql $querybuilder2从部分?

Answer 1

这应该是你想要的

它将更新状态和updatedAt，仅对于'pending'和'done'事务

getSingleScalarResult()将告诉您更新了多少事务，

根据需要修改

return $this->getEntityManager()
->createQueryBuilder()
->update(Transaction::class, 't')
->set('t.status', 'CASE WHEN t.status = :statusPending THEN :statusDone ELSE :statusInProgress END')
->set('t.updatedAt', ':updatedAt')
->where('t.status IN (:statuses)')
->andWhere('t.timeoutAt is NOT NULL')
->andWhere('t.timeoutAt < :timeoutAt')
->setParameter('statuses', ['pending', 'done'])
->setParameter('updatedAt', Carbon::now(TimezoneEnum::UTC))
->setParameter('statusPending','pending')
->setParameter('statusDone', 'done')
->setParameter('statusInProgress', 'progress')
->setParameter('timeoutAt', Carbon::now(TimezoneEnum::UTC))
->getQuery()
->getSingleScalarResult();