在不同的聊天中获得一名用户的所有对话MYSQL [英] Get all conversations from one user in different chats MYSQL

问题描述

我正在进行一个Facebook风格的聊天，并试图从一个用户与其他用户获得所有最新的对话. 目前，我正在获得所有最新的对话，但是如果其他用户已写信给我.我想做的是例如我有2个对话，对话1是我写入的最新对话，但是现在我写入对话2，刷新页面时，对话2将是第一个对话. 现在，如果对话1的另一个人给我写信，那么如果我刷新页面，对话1将是第一个. 这是我正在使用的2个表:

I am working on a facebook style chat and am trying to get all the latest conversations from one user with other users. Right now I am getting all the latest conversations but in the case that the other user has written to me. What I want to do is for example I have 2 conversations, conversation 1 is the latest I wrote into, but now I write into conversation 2, when I refresh the page conversation 2 will be first. Now if the other person of conversation 1 writes me, if I refresh page conversation 1 will be first. This are the 2 tables I am working with:

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消息列表中查看您的最新对话.

In this case :id_us could be for example 1 I know its not correct as it is not doing what I would like it to do, but I've been struggling trying to find the way to get the results as I want, but I am stucked, any help will be apreciated. To explain myself better, what I am trying to do is for example when you chat in facebook, the facebook messages list to view your latest conversations.

推荐答案

确定如何:(使用123作为user1的ID)

OK how about this: (using 123 for id of user1)

   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

这篇关于在不同的聊天中获得一名用户的所有对话MYSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！