MySql仅按时间戳按顺序从不同线程中选择最新消息-类似于Facebooks(2013)的专用消息收件箱 [英] MySql SELECT only newest message from distinct threads order by timestamp -Private Messages Inbox Similar to Facebooks(2013)
问题描述
试图重新创建类似于Facebook最近设置的私人消息系统.我有问题的部分是按时间戳仅选择来自不同线程的最新消息.
Trying to recreate a private message system similar to what Facebooks setup these days. The part im having a problem with is SELECT only newest message from distinct threads order by timestamp.
这是到目前为止我可以提出的最接近的查询.问题是收件箱中每个线程只能返回一行,并且当前我的查询从同一线程返回多行:
Here is the closest query I could come up with so far. The problem is the inbox should only return one row for each thread and currently my query returns multiple rows from the same thread:
SELECT m.created_on, m.thread_id, m.message_id, m.created_by, m.body, u.first_name, u.last_name, u.thumb_img
FROM pms_messages AS m, pms_recips AS r, users AS u
WHERE r.uid =19
AND r.status
IN ('A', 'N'
)
AND r.thread_id = m.thread_id
AND (SELECT max(r.message_id)) = m.message_id
AND m.created_by = u.uid
ORDER BY created_on DESC
这是我当前数据库设置的img,如果可以简化查询并完成工作,则可以打开对表的更改.下方是我的表格/收件箱规格的更详细说明.
Here is a img of my current db setup , im open to changes to my tables if it would simplify my querys and get the job done. Further down is more detailed explanation of my tables/the inbox specs.
更具体地说:
*任何两个用户之间的消息都在一个正在进行的线程上发生.任何两个用户之间的所有消息只是一个正在进行的对话(线程)的延续,即使两个用户删除彼此之间的所有先前消息,以后任何消息仍将在同一线程上发生.
*Messages between any two users occurs on a single , ongoing thread. All messages between any 2 users is just a continuation of one ongoing conversation(thread) Even if both users delete all previous messages between each other any future messages will still occur on the same thread.
*单个消息由message_id跟踪.
*individual messages are tracked by message_id.
*每个线程在任何给定时间仅在收件箱中显示一次,并将显示该线程的最新消息.
*Each thread is only displayed once in the inbox at any given time and will display the most recent msg from the the thread.
*在同一收件箱中将显示接收和发送的邮件,而不是收件箱和发件箱.
*Instead of a inbox and a outbox messages being received and sent will be displayed in the same inbox.
因此,如果即时通讯用户A和我有一个消息,我在20分钟前还没有从用户B读取过,我回复了一个消息用户C,昨天(10分钟前)给我发送了消息,又给用户D发出了另一条消息,从用户D的另一个消息-2分钟前,我给了我一个消息.收件箱应如下所示:
So if im User A and I have a message I havent read yet from user B-20 minutes ago, I responded to a msg user C sent me yesterday- 10 minutes ago and another message from user D - 2 minutes ago my inbox should look like this:
具有USER D的线程-显示味精用户D发送给我.
Thread with USER D - displaying msg user D sent me.
带有USER C-的线程-显示我发送给用户C的味精
Thread with USER C- - displaying msg I sent to user C
具有USER B的线程-显示味精用户B发送给我
Thread with USER B - displaying msg user B sent me
*正在显示的线程将按最新到最旧的顺序进行排序.
*the threads being displayed will be sorted by most recent - to oldest .
推荐答案
SELECT * FROM
(SELECT * FROM messages ORDER BY created_at DESC) messages
GROUP BY IF(from_user_id < to_user_id, CONCAT_WS('_', from_user_id, to_user_id), CONCAT_WS('_', to_user_id, from_user_id));
这篇关于MySql仅按时间戳按顺序从不同线程中选择最新消息-类似于Facebooks(2013)的专用消息收件箱的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!