Ben Ben - 7 months ago 72
SQL Question

Convert MySQL with Subqueries to DQL 1.2

Hi I have the following query from a previous question and need to convert it to DQL for Doctrine 1.2. However it turns out that DQL does not support subqueries in joins.

SELECT * FROM contact c
LEFT JOIN
(SELECT a1.contact_id, a1.date, a1.activity_type_id FROM activity a1
JOIN (SELECT contact_id, MAX(DATE) DATE FROM activity GROUP BY contact_id) a2
ON a1.contact_id = a2.contact_id AND a1.date = a2.date
) a
ON c.id = a.contact_id

WHERE a.activity_type_id = 2;


I'm trying to figure out how to do this another way without resorting to multiple queries.

Thanks.

Ben Ben
Answer

Final query:

SELECT * FROM contact c
    LEFT JOIN activity ON c.id = contact_id
    WHERE ROW (c.id,DATE) IN (SELECT contact_id, MAX(date) date FROM activity  GROUP BY contact_id)
    AND activity_type_id = 2

Final DQL:

$q->from('Contact c') 
->leftJoin('c.Activity a')      
->where('ROW (c.id, date) IN (SELECT a1.contact_id, MAX(a1.date) a1.date FROM Activity a1 GROUP BY a1.contact_id)')
->andWhere('a.activity_type_id = ?', $filterActivityTypeId);
Comments