Join a subquery with Doctrine DQL

Question

Using Doctrine in Symfony2, I need to recover each items and for each of them, the latest timestamp of their report. So I would like to execute a query using DQL, which would be like this in SQL:

SELECT * from `item` i
LEFT JOIN `kit` k ON k.`id` = i.`kit_id`
LEFT JOIN 
    (SELECT e.`item_id`, MAX(e.`dateCreation`) 
    FROM `entete_rapport` e
    GROUP BY e.`item_id`) latest ON latest.`item_id` = i.`id`

I am not able to have the same with DQL. I guess I have to separate the subquery et the main one, with something like this:

$subSelect->select('e AS ItemId, MAX(e.dateCreation) AS latest')
                ->from('CATUParkBundle:EnteteRapport', 'e')
                ->groupBy('e.item');

$qb->select('i')
                ->from('CATUParkBundle:Item', 'i')
                ->leftJoin('i.kit', 'k')
                ->leftJoin('CATUParkBundle:EnteteRapport f', sprintf('(%s)', $subSelect->getDQL()), 'latest', 'f.id = latest.ItemId');

I am not able to make this query work, I really need you guys. Thank you in advance, you're awesome!

score 0 · Answer 1 · edited May 23 '17 at 12:09

Seems like subqueries in joins do not work in dql. (I get error message: [Semantical Error] line 0, col 52 near '(SELECT e': Error: Class '(' is not defined.)

You could run $em->getConnection()->prepare($yourRawSql)->execute()->fetchAll(), but this returns a raw result array (no objects). See raw sql queries on knp-lab

Or do it in dql with HAVING instead of a subquery join:

$qb->select('i', 'i')
    ->addSelect('MAX(e.dateCreation)', 'date')
    ->from('CATUParkBundle:Item', 'i')
    ->leftJoin('i.kit', 'k')
    ->leftJoin('i.rapportOrWhatEver', 'f')
    ->groupBy('e.id');

Join a subquery with Doctrine DQL

1 Answers1