在不同的聊天 MYSQL 中获取一个用户的所有对话

Question

我正在做一个Facebook风格的聊天，并试图从一个用户与其他用户那里获取所有最新的对话。现在我正在收到所有最新的对话，但如果其他用户写信给我。我想做的是，例如我有 2 个对话，对话 1 是我最新写进的，但现在我写进对话 2，当我刷新页面时，对话 2 将是第一个。现在，如果对话 1 的其他人写信给我，如果我刷新页面，对话 1 将是第一个。这是我正在使用的 2 个表：

CREATE TABLE users (
    id int(255) not null auto_increment,
    username varchar(150) null,
    email varchar(150) null,
    password varchar(255) null,
    salt varchar(255) null,
    pic varchar(255) not null,
    primary key(id)
) ENGINE = INNODB DEFAULT CHARSET=utf8;
CREATE TABLE chat (
    id int(255) not null auto_increment,
    id_us int(255) not null,
    id_receptor int(255) not null,
    message varchar(3000) not null,
    chat_date datetime not null,
    primary key (id),
    index chatUsId(id_us),
    index chatRecId(id_receptor),
    foreign key (id_us) references users(id) on delete cascade,
    foreign key (id_receptor) references users(id) on delete cascade
) ENGINE = INNODB DEFAULT CHARSET=utf8;

这是我正在使用的查询

SELECT m.id_us, m.id_receptor, u.username, u.pic 
FROM users AS u, chat AS m
WHERE (m.id_us = u.id  AND m.id_receptor =:id_us or m.id_receptor = u.id AND m.id_us =:id_us)
AND m.chat_date = (SELECT MAX( c.chat_date ) FROM chat AS c WHERE m.id_us = c.id_us AND m.id_receptor = c.id_receptor ) 
group by u.username
ORDER BY m.id DESC 

在这种情况下：id_us可以是例如 1我知道这是不正确的，因为它没有做我想让它做的事情，但我一直在努力寻找获得我想要的结果的方法，但我被困住了，任何帮助都会得到赞赏。为了更好地解释自己，我试图做的是例如，当您在Facebook上聊天时，Facebook消息列表以查看您的最新对话。

Answer 1

好的，

怎么样：(使用 123 作为用户 1 的 ID(

   select T1.user2_id, users.username, users.pic, max(cdate) maxDate from
   (select chat.id_receptor user2_id, max(chat_date) cdate
   from chat 
   where chat.id_us=123
   group by chat.id_receptor
   union distinct
   (select  chat.id_us user2_id, max(chat_date) cdate
   from chat  where chat.id_receptor = 123
   group by chat.id_us)) T1
   inner join users on (users.id = T1.user2_id)
   group by T1.user2_id
   order by maxDate desc

Answer 2

我也想要最近的消息，比如whatsapp，我已经根据我的表格更新了你的查询。

select T1.user2_id, message, user_profile.user_name, user_profile.avatar, max(cdate) message_date from (select messages.receiver_id user2_id, messages.message, (message_date) cdate from messages where messages.sender_id=2 group by messages.receiver_id union distinct(select  messages.sender_id user2_id, messages.message, max(message_date) cdate from messages  where messages.receiver_id = 2 group by messages.sender_id)) T1 inner join user_profile on (user_profile.id = T1.user2_id) group by T1.user2_id order by message_date desc

Answer 3

刚刚添加了另一个选择 + JOIN。请参考以下内容，如果有任何疑问，请告诉我，将尝试回答。

我想指出的是，即使上述答案被标记为正确，但据我了解，它并没有涵盖请求查询的全部需求，因为没有显示关键项目 - 即每次聊天的最后一条消息。

$sql="
SELECT T2.maxDate, T2.user2_id, T2.ava, T2.userName,chat.user_to,chat.user_from,chat.body,chat.viewed FROM (SELECT T1.user2_id, users.userName, users.ava, max(cdate) maxDate FROM
            (SELECT chat.user_to user2_id, max(msg_time) cdate
                FROM chat WHERE chat.user_from=18
                GROUP BY chat.user_to
            union distinct
            (SELECT chat.user_from user2_id, max(msg_time) cdate
                FROM chat WHERE chat.user_to=18
                GROUP BY chat.user_from)) T1
            inner join users on (users.userID = T1.user2_id)
            group by T1.user2_id
            order by maxDate desc) T2
            join chat on (T2.maxDate = chat.msg_time) ORDER BY T2.maxDate DESC";