php - 检索自上次访问/读取以来已更新时间戳的记录

Question

以下是我试图解决的问题的简化版本。我就是想不通。

我有两个表:

Table 1: messages
id | timestamp | message
------------------------
1 | 2014-01-20 09:00:00 | Hello I'm a message
2 | 2014-01-20 09:00:00 | Second message for you
3 | 2014-01-27 11:00:00 | This message has been updated
4 | 2014-01-28 13:45:00 | Last message for now

和

Table 2: reads
id | message_id | user_id | last_read_timestamp
------------------------
1 | 1 | 1 | 2014-01-20 09:10:00
1 | 2 | 1 | 2014-01-20 09:15:00
1 | 3 | 1 | 2014-01-24 19:25:00
1 | 1 | 2 | 2014-01-28 13:45:00

表a中的id与表2中的message_id相关。

表A中的消息更新频繁，并且时间戳更新。因此，我需要运行一个查询，该查询将返回自特定 user_id 读取以来已更新的任何消息的表 A id，因为用户尚未读取该消息(因此表 2 中没有出现相应的条目。

因此，对于 user_id = 1，message_id 3 自读取后已更新，message_id 4 是一条新消息。 user_id = 1 的结果将是:

Unread
------
3
4

我尝试过在表之间进行 LEFT JOIN，但似乎无法获得正确的结果。

Answer 1

如果您只需要检索单个用户未读的消息，您可以使用如下内容:

SELECT messages.id AS unread
FROM
  messages LEFT JOIN (SELECT message_id, MAX(last_read_timestamp) as last_rd
                      FROM reads
                      WHERE user_id = 1
                      GROUP BY message_id) lr
  ON messages.id = lr.message_id
     AND messages.timestamp < lr.last_rd
WHERE
  lr.message_id IS NULL