How to use the RETURNING update query into the DQL

Question

I'm trying to run these following simple query (it works in postgresql)

update ja_clients set ref_inc_num = ref_inc_num + 1  where id = 43933 returning ref_inc_num;

into DQL

$query = $this->entityManager->createQuery();
            $query->setDQL("UPDATE Geoop\Core\Entity\Account a SET a.jobReferenceNumber__ = a.jobReferenceNumber__ + 1  WHERE a.id = :accountId  RETURNING a.jobReferenceNumber__");
            $query->setParameter('accountId', $account->getId());
            $total = $query->getSingleScalarResult();

but it doesn't work retuning this error:

#30 /var/www/geoop_api/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php (line 861): Doctrine\ORM\Query\Parser->syntaxError(end of string)
#31 /var/www/geoop_api/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php (line 448): Doctrine\ORM\Query\QueryException::dqlError(UPDATE Geoop\Core\Entity\Account a SET a.jobReferenceNumber__ = a.jobReferenceNumber__ + 1  WHERE a.id = :accountId  RETURNING a.id as id)

Answer 1

DQL does not support the RETURNING clause, so you need to use native SQL. You will also have to create a result set mapping.

NOTE: I don't know the actual names of your table and columns, but assuming they are named the same as your entity and properties, this will work:

$rsm = new \Doctrine\ORM\Query\ResultSetMapping();
$rsm->addScalarResult('jobReferenceNumber', 'jobReferenceNumber');

$sql = "
    UPDATE account a
    SET a.jobReferenceNumber = a.jobReferenceNumber + 1
    WHERE a.id = :accountId
    RETURNING a.jobReferenceNumber
";

$query = $this->entityManager->createNativeQuery($sql, $rsm);
$query->setParameter('accountId', $account->getId());
$total = $query->getSingleScalarResult();

You may need to correct the table and column names in my example. When using native SQL, you have to use the actual names in the database, not the names of entity and properties. Also, remember that postgres converts all object names to upper case unless they are quoted.

Answer 2

thanks for the insight :) this is working now

Heres my updated code : Above the class i use the ResultSetMapping

use Doctrine\ORM\Query\ResultSetMapping;

$rsm = new ResultSetMapping();
$rsm->addScalarResult('ref_inc_num', 'ref_inc_num');
$sql = "
UPDATE ja_clients
SET ref_inc_num = ref_inc_num + 1
WHERE id = :clientId
RETURNING ref_inc_num
";
$query = $this->entityManager->createNativeQuery($sql, $rsm);
$query->setParameter('clientId', $account->getId());
$refNum = $query->getSingleScalarResult();