我使用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从部分?
这应该是你想要的
它将更新状态和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();