Mysql 在 stackoverflow 上查询通知的最佳方式

Question

我有 4 个表:

comments
+----+-----------+--------------+-------+
| id | content   | user_id | article_id |
+----+-----------+--------------+-------+
| 1  | Comment 1 | 2       | 5          |
| 2  | Comment 2 | 5       | 3          |
| 3  | Comment 3 | 1       | 6          |
| 4  | Comment 4 | 6       | 8          |
| 5  | Comment 5 | 1       | 6          |
| ...| ...       | ...     | ...        |
+----------------+---------+------------+

votes
+----+----------+--------------+---+
| id | type | user_id | article_id |
+----+----------+--------------+---+
| 1  | 1    | 2       | 5          |
| 2  | 1    | 3       | 3          |
| 3  | 0    | 1       | 6          |
| 4  | 1    | 7       | 4          |
| 5  | 0    | 9       | 4          |
| 6  | 0    | 1       | 6          |
| ...| ...  | ...     | ...        |
+------------+----------+----------+

notifications (object_id is the id of the vote|comment)
+----+----------+--------------+-------------+-------------+--------------+
| id | object_url| object_id |activitytype_id|  sender_id  | recipient_id | 
+----+----------+------------+---------------+-------------+--------------+
| 1  | /../../.. | 1         | 2             |       2     |       6      | 
| 2  | /../../.. | 2         | 2             |       3     |       2      | 
| 3  | /../../.. | 1         | 1             |       2     |       7      | 
| 3  | /../../.. | 2         | 1             |       5     |       2      | 
| 3  | /../../.. | 3         | 1             |       1     |       3      | 
| 3  | /../../.. | 3         | 3             |       1     |       2      | 
| 3  | /../../.. | 4         | 2             |       7     |       8      |
| 3  | /../../.. | 5         | 3             |       9     |       1      |
| 3  | /../../.. | 6         | 3             |       1     |       5      | 
| ...| ...       | ...       | ...           |             |              | 
+----+-----------+-----------+---------------+-------------+--------------+

activitytypes
+----+------------+
| id |    label   |
+----+------------+
| 1  | comment    |
| 2  | vote up    |
| 3  | vote down  |
| ...| ...        |
+-----------------+

我想在 stackoverflow 上收到通知。

我想为特定用户的每个 activitytype 和 object_url 组合查询最后一个通知(如果事件类型是评论，则带有评论内容；如果不是，则为 null)。

例如，我有 3 个文章 A、B 和 C，它们都有 3 个评论、4 个赞成票和 2 个反对票。如何获取每篇文章的最后评论、投赞成票和投反对票？

我试过这个查询:

SELECT n.id, n.object_url , n.object_id, n.activitytype_id, IF(n.activitytypeId = 1, 
(SELECT content FROM comments WHERE id=n.object_id), null) AS activitycontent     
FROM notifications n WHERE n.recipient_id =1
GROUP BY n.activitytype_id,n.object_url 
ORDER BY n.id DESC

但它不起作用。谁能帮忙？

编辑:farhadamjady 的回答中的以下查询给了我第一条评论:

SELECT
n.id,
n.object_url,
n.object_id,
n.activitytype_id,
cm.content AS activitycontent
FROM
  notifications n
LEFT OUTER JOIN `COMMENT` AS cm ON cm.id = n.object_id and n.activitytypeId  = 1
WHERE
  n.recipient_id = 1
GROUP BY
  n.activitytype_id,
  n.object_url
HAVING MAX(cm.id)
ORDER BY
  n.id DESC

我怎样才能把它改成最后一个？

Answer 1

你应该像这样使用左外连接:

SELECT
n.id,
n.object_url,
n.object_id,
n.activitytype_id,
cm.content AS activitycontent
FROM
  notifications n
 LEFT OUTER JOIN `COMMENT` AS cm ON cm.id = n.object_id and n.activitytypeId  = 1
WHERE
  n.recipient_id = 1
GROUP BY
   n.activitytype_id,
   n.object_url
 HAVING MAX(cm.id)
ORDER BY
  n.id DESC