将带子查询的MySQL转换为DQL 1.2(Convert MySQL with Subqueries to DQL 1.2)

将带子查询的MySQL转换为DQL 1.2(Convert MySQL with Subqueries to DQL 1.2)

嗨，我从上一个问题得到以下查询，需要将其转换为DQL for Doctrine 1.2。 但事实证明，DQL不支持连接中的子查询。

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;

我试图弄清楚如何在不诉诸多个查询的情况下以另一种方式做到这一点。

谢谢。

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.

最满意答案

使用doctrine，您不应该使用原始sql将子查询嵌套到where条件中。 在更复杂的情况下，您将遇到麻烦。 而是使用createSubquery明确告诉有关子查询的学说，让doctrine将其编译为DQL，然后将其嵌入到where条件中。 所以你的查询应该是这样的：

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

另一个例子可以在这里找到：

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

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);将带子查询的MySQL转换为DQL 1.2(Convert MySQL with Subqueries to DQL 1.2)

嗨，我从上一个问题得到以下查询，需要将其转换为DQL for Doctrine 1.2。 但事实证明，DQL不支持连接中的子查询。

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;

我试图弄清楚如何在不诉诸多个查询的情况下以另一种方式做到这一点。

谢谢。

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.

最满意答案

使用doctrine，您不应该使用原始sql将子查询嵌套到where条件中。 在更复杂的情况下，您将遇到麻烦。 而是使用createSubquery明确告诉有关子查询的学说，让doctrine将其编译为DQL，然后将其嵌入到where条件中。 所以你的查询应该是这样的：

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

另一个例子可以在这里找到：

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

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