MySQL在两个用户之间进行对话 [英] MySQL get a conversation between two users

问题描述

我有一个名为private_messages的SQL表，带有字段(id，from，to，message，stamp). 标记字段对应于消息的日期

I have a SQL table named private_messages with fields (id,from,to,message,stamp). the stamp field corresponds to the date of the message

那么我需要什么查询:

1)在两个用户之间进行对话(按日期排序)?

1) get a conversation between two users (ordered by date)?

我已经尝试过查询

(SELECT * FROM private_messages WHERE from=$my_id AND to=$other_id) 
UNION 
(SELECT * FROM private_messages WHERE from=$other_id AND to=$my_id) 
ORDER BY stamp
;

但不起作用...

2)是否按日期将我和其他用户之间的最后一条消息(每个用户都有不同的用户)按日期排序(例如，像在faceebook中那样构造收件箱)?

2) get the last messages beetween me and other users, each one with a different user, ordered by date (to construct a inbox like in faceebook for example)?

推荐答案

1.)

SELECT  * 
FROM    private_messages a
WHERE   (a.from = $my_id AND a.to = $other_id) OR
        (a.from = $other_id AND a.to = $my_id)
ORDER   BY stamp DESC

2.)

SELECT  f.*
FROM
        (
            SELECT  *
            FROM    private_messages a
            WHERE  (LEAST(a.from, a.to), GREATEST(a.from, a.to), a.stamp) 
                    IN  (   
                            SELECT  LEAST(b.from, b.to) AS x, 
                                    GREATEST(b.from, b.to) AS y,
                                    MAX(b.stamp) AS msg_time
                            FROM    private_messages b
                            GROUP   BY x, y
                        )
        ) f
WHERE   $my_id IN (f.from, f.to)
ORDER   BY f.stamp DESC

这篇关于MySQL在两个用户之间进行对话的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！