所以我有两个表来处理消息threads
和messages
。
messages:
id | thread | user_from | user_to | text | created
threads:
id | user_from | user_to | created | status
我想构建一个查询,该查询将通过以下方式获取我的线程:
- 该线程的最新消息
- 该线程的 ID
到目前为止,我一直在通过首先获取所有线程(当然有适当的限制)然后分别获取每个线程的最新消息来处理这个问题。
有什么想法吗?
更新:
表关系:messages
.thread
= threads
.id
在我的头顶上:
SELECT t.id, m.* FROM threads t
INNER JOIN messages m ON m.thread = t.id
WHERE m.id =
(SELECT id FROM messages WHERE thread = t.id ORDER BY id DESC LIMIT 1);
[编辑] 经过测试,似乎工作正常。
select t.id, max(m.id) as last_message_id
from threads t
left outer join messages m on m.thread = t.id
group by t.id
试试这个
SELECT a.id AS ThreadID,
c.`text` AS LatestMessage
FROM threads a
INNER JOIN
(
SELECT thread, max(created) maxCreated
FROM messages
GROUP BY thread
) b on a.id = b.thread
INNER JOIN messages c
on a.id = c.thread AND
b.maxCreated = c.created