How to update from subquery with query builder?-CodePudding

I'm using Symfony 5, Doctrine, and PostgreSQL.

In some Transaction Repository I'm trying to run the code below:

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

After calling $queryBuilder->getQuery()->getResult(); I got an error:

Doctrine\ORM\Query\QueryException : [Syntax Error] line 0, col 37: Error: Expected Doctrine\ORM\Query\Lexer::T_SET, got ','

If to remove ->from($queryBuilder2->getDQL(), 't') error will be fixed, but I need this FROM.

Also $queryBuilder2->getQuery()->getResult() working perfect;

How to correctly put sql of $querybuilder2 to FROM part?

CodePudding user response：

this should be what you want

it will update the status and the updatedAt, for only 'pending' and 'done' transations

the getSingleScalarResult() will let you know how much transactions got updated,

change it as you need

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();