Join a subquery with Doctrine DQL
<p>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:</p>

<pre><code>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`
</code></pre>

<p>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:</p>

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

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

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