MySQL获取最新的对话消息 [英] MySQL get latest conversation messages

问题描述

我正在开发一个应用程序，用户可以在其中与其他用户聊天(1对1，而不是群聊).我有一个MySQL表，用于存储来自每个用户的所有消息，例如:

I'm making an app where users can chat to other users (1 on 1, NOT a group chat). I have a MySQL table that stores all the messages from every user, like:

from_id   to_id          message             time
abc123    def456         Hello               789
def456    abc123         What's up?          1234`
def456    abc123         How was last night? 2345
abc123    p0tat0         I missed the bus    3456
def456    p0tat0         I hate you :(       4567`
def456    another_user   I hate Potato!      5678`

如何将AND的最新消息从abc123排序为从最新到最旧的消息，例如:

How can I get the latest message from AND to abc123 sorted from newest to oldest, like:

from_id to_id message time

from_id to_id message time

abc123 p0tat0 I missed the bus 3456

def456 abc123 How was last night? 2345

time在消息表中将始终按升序排列.

time will always be in an ascending order in the messages table if that matters.

任何帮助将不胜感激.谢谢^.^

Any help would be very much appreciated. Thank you ^.^

SELECT *, 'from' as direction FROM messages WHERE from_username='admin' AND 'time' = ( SELECT MAX('time') FROM messages WHERE from_username='admin' OR to_username='admin' )

UNION ALL

SELECT *, 'to' as direction FROM messages WHERE to_username='admin' AND 'time' = ( SELECT MAX('time') FROM messages WHERE to_username='admin' OR to_username='admin' )

推荐答案

尝试一下

SELECT * FROM
messageTable
WHERE from_id='abc123'
AND `time` = ( SELECT MAX(`time`) FROM messageTable WHERE from_id='abc123' )

UNION ALL

SELECT * FROM
messageTable
WHERE to_id='abc123'
AND `time` = ( SELECT MAX(`time`) FROM messageTable WHERE to_id='abc123' )

这篇关于MySQL获取最新的对话消息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！