Symfony Doctrine - use CTE with query builder

Question

I'm failing to use a Common Table Expression with symfony.

I have a class App\Entity\User and a class App\Entity\Account

I want to collect account available to a list of user and all the accounts not associated to any user.

I would want to pass this query to the symfony query builder

WITH cte (userid, accountid) AS (
  SELECT u.id as userid, a.id as accountid
  FROM App\Entity\User u
  JOIN App\Entity\Account a ON (a.id = u.account)
)
SELECT DISTINCT(accountid)
FROM App\Entity\Account a
LEFT JOIN cte c ON (a.id = c.accountid)
WHERE c.userid IN (1,2,3)
OR NOT EXISTS (SELECT NULL FROM cte c2 WHERE c2.accountid = a.accountid)
ORDER BY 1

I would like the query builder to transform the class to the table associated.

I tried to use

$rsm = new ResultSetMapping();
$rsm->addEntityResult('App\Entity\User','u');
$rsm->addFieldResult('u','id', 'id');
$rsm->addEntityResult('App\Entity\Account','a');
$rsm->addFieldResult('a','id', 'id');

but it does not transform the tables.

Any idea ?

Note: using symfony:4.4 and doctrine/dbal:2.10.3

Symfony Doctrine - use CTE with query builder

0 Answers0