Convert MySQL with Subqueries to DQL 1.2

Question

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.

I have never used a view but doctrine appears to support them. — Ben, Aug 08 '12 at 02:47
I have read about views in doctrine and it wont solve it for me.. I am going to have to try and rewrite this as a DQL query but I don't know if it is possible to do it. — Ben, Aug 08 '12 at 07:21
So, can you for instance create a view `CREATE VIEW max_activity_date AS SELECT contact_id, MAX(date) AS max_date FROM activity GROUP BY contact_id` and join to that? — Neil, Aug 08 '12 at 23:55
I managed to rewrite it. Views didn't work but was able to do it different way. Thanks. — Ben, Aug 10 '12 at 13:33

philipphoffmann · Answer 1 · 2016-04-16T19:45:23.923

Using doctrine, you should not nest subqueries into where condition using raw sql. You will get into trouble in more complex scenarios. Instead use createSubquery to explicitly tell doctrine about the subquery, let doctrine compile it to DQL and then nest it into your where condition. So your query should look something like this:

$q->from('Contact c') 
  ->leftJoin('c.Activity a')
;

$subquery = $q->createSubquery()
    ->select("a1.contact_id, MAX(a1.date) a1.date")
    ->from("Activity a1")
    ->groupby("a1.contact_id")
;

$q->where('ROW (c.id, date) IN ('.$subquery->getDql().')')
  ->andWhere('a.activity_type_id = ?', $filterActivityTypeId)
;

Another example can be found here:

https://www.philipphoffmann.de/2012/08/29/a-bulletproof-pattern-for-creating-doctrine-subqueries-of-any-complexity/

Ben · Accepted Answer · 2012-08-10T13:41:39.560

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);

Convert MySQL with Subqueries to DQL 1.2

2 Answers2