0

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

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)

Tables are structured as follows:

A contact can have many activities of varying types

activity:
id
contact_id
activity_type_id
date

contact:
id
first_name
last_name

I have this so far:

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 */

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

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

Thankyou.

The Final Solution

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;

The final where cause can be adjusted to return various activity type or set to IS NULL.

DQL 1.2 Compatible version

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
Ben
  • 380
  • 1
  • 3
  • 14

1 Answers1

1

Try this query -

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;
Devart
  • 119,203
  • 23
  • 166
  • 186
  • Wonderful. Thank you.. I will need to spend some time figuring this out.. it should be interesting to convert to DQL. I had to make some minor corrections. I have put the final query in the question. – Ben Aug 07 '12 at 08:01
  • OK, I will explain a little. The subquery returns a subset of correct rows - the most recent rows by `contact_id`. – Devart Aug 07 '12 at 08:15
  • Is it possible to do this without sub queries ? – Ben Aug 09 '12 at 06:40
  • No, you cannot avoid using subquery, because you should select exact record from the `activity` table and then filter by `activity_type_id` field. I have added this field in my answer too;-) – Devart Aug 10 '12 at 12:52
  • Thanks I was able to rewrite this to make it DQL compatible and have edited the question. – Ben Aug 10 '12 at 13:37