Symfony Doctrine – use CTE with query builder

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

I have a class AppEntityUser and a class AppEntityAccount

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 AppEntityUser u
  JOIN AppEntityAccount a ON (a.id = u.account)
)
SELECT DISTINCT(accountid)
FROM AppEntityAccount 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('AppEntityUser','u');
$rsm->addFieldResult('u','id', 'id');
$rsm->addEntityResult('AppEntityAccount','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

Source: Symfony Questions

Was this helpful?

0 / 0

Leave a Reply 0

Your email address will not be published. Required fields are marked *