MySQL 查询(或 Doctrine 1.2 查询)- 从连接表和过滤器中获取最新项目

Question

我在构造查询来执行以下操作时遇到问题:

按activity_type_id过滤联系人，仅显示最近事件具有所需activity_type_id或为NULL(无事件)的联系人

表格结构如下:

一个联系人可以有许多不同类型的事件

activity:
id
contact_id
activity_type_id
date

contact:
id
first_name
last_name

到目前为止我有这个:

SELECT * FROM (
    SELECT c.first_name, c.last_name, a.activity_type_id, MAX(a.date) AS maxdate 
    FROM contact AS c
    LEFT JOIN activity AS a ON a.contact_id = c.id
    GROUP BY c.id
    ORDER BY c.first_name  ASC
) AS act

然后将其添加到过滤器:

WHERE activity_type_id = 3 /* <- I would like to filter using this */

但是，对于具有多个事件的联系人，我得到了错误的 Activity_type_id。

我最终想将其用作 Doctrine 1.2 查询，但我希望首先在 MySQL 中工作。

谢谢。

最终解决方案

SELECT c.first_name, c.last_name, a.activity_type_id 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;

最后的 where 原因可以调整为返回各种事件类型或设置为 IS NULL。

DQL 1.2 兼容版本

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

Answer 1

尝试这个查询 -

编辑:添加a1.activity_type_id

SELECT c.first_name, c.last_name FROM contacts 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.contact_id = a.contact_id
WHERE a.contact_id IS NULL OR a.activity_type_id = 3;