MySQL查询(或Doctrine 1.2查询)-从联接表和过滤器中获取最新项目 [英] MySQL Query (or Doctrine 1.2 query) - get most recent item from joined table and filter

问题描述

我无法构造查询来执行以下操作:

I am having trouble constructing a query to do the following:

按activity_type_id筛选联系人，仅显示最近活动具有所需activity_type_id或为NULL(无活动)的联系人

Filter contacts by activity_type_id, only displaying contacts where the most recent activity has the desired activity_type_id or is NULL (no activity)

表的结构如下:

联系人可以进行许多不同类型的活动

A contact can have many activities of varying types

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

然后将其添加到过滤器中:

then adding this to filter:

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

但是对于有多个活动的联系人，我得到了错误的activity_type_id.

However I am getting the wrong activity_type_id for contacts that have multiple activities.

我最终希望将此作为Doctrine 1.2查询使用，但我希望首先使事情在MySQL中起作用.

I ultimately would like to use this as a Doctrine 1.2 query but I like to get things working in MySQL first.

谢谢.

最终解决方案

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;

最终原因可以调整以返回各种活动类型或设置为IS NULL.

The final where cause can be adjusted to return various activity type or set to 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

推荐答案

尝试此查询-

a1.activity_type_id已添加

edit: a1.activity_type_id is added

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;

这篇关于MySQL查询(或Doctrine 1.2查询)-从联接表和过滤器中获取最新项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！