从表中查找最新消息，按用户在mysql中分组 [英] Finding latest message from table, grouped by user in mysql

问题描述

我的网站上有一个私人消息表，现在有一段时间，我的收件箱和发件箱是分开的.我只想组合收件箱/发件箱，只显示发给特定用户或来自特定用户的最新消息.

I have a private messages table on my site and for a while now I have had an inbox and sentbox separate. I want to combine the inbox/sentbox only showing the latest message either to or from a specific user.

TLDR:我想显示按发送给每个用户或来自每个用户的分组的最新消息.

TLDR: I want to show the latest message grouped by sent to or from each user.

表格示例

|  id | fromuser | fromid | touser | toid | message    |  timestamp            |
--------------------------------------------------------------------------------
|  1  | user1    |  1     | user2  |  2   | Hello..    |  2015-01-01 00:00:00  |
|  2  | user1    |  1     | user3  |  3   | okay...    |  2015-01-02 00:00:00  |
|  3  | user3    |  3     | user1  |  1   | not....    |  2015-01-03 00:00:00  |
|  4  | user2    |  2     | user3  |  3   | New....    |  2015-01-04 00:00:00  |
|  5  | user2    |  2     | user1  |  1   | With.....  |  2015-01-05 00:00:00  |
--------------------------------------------------------------------------------

我正在寻找用户1中的结果

Result i'm looking for when in user 1

|  id | fromuser | fromid | touser | toid | message    |  timestamp            |
--------------------------------------------------------------------------------
|  3  | user3    |  3     | user1  |  1   | not....    |  2015-01-03 00:00:00  |
|  5  | user2    |  2     | user1  |  1   | With.....  |  2015-01-05 00:00:00  |
--------------------------------------------------------------------------------

使用下面的代码，我可以让它向每个用户显示一条消息或从每个用户那里显示一条消息，但它显示的是最早的消息，而不是最新的消息.

Using the code below I can get it to show one message to or from each user but it shows the oldest message not the newest.

mysql_query("SELECT m1.*, users.id AS userid, users.username 
    FROM pm AS m1, pm AS m2, users 
    WHERE ((m1.fromuser='".$_SESSION['userName']."' AND users.id=m1.toid) 
    OR (m1.touser='".$_SESSION['userName']."' AND users.id=m1.fromid)) 
    AND m2.id=m1.id ORDER BY timestamp DESC");

推荐答案

尝试一下:

选择 * 来自消息m 不存在的地方( 选择1 从消息毫米 其中(mm.fromuser = m.fromuser或mm.fromuser = m.touser)AND(mm.touser = m.touser或mm.touser = m.fromuser) 和mm.timestamp> m.timestamp ) 和m.fromuser ='user1'或m.touser ='user1';

select * from messages m where not exists ( select 1 from messages mm where (mm.fromuser = m.fromuser or mm.fromuser = m.touser) AND (mm.touser = m.touser or mm.touser = m.fromuser) and mm.timestamp > m.timestamp ) and m.fromuser = 'user1' or m.touser = 'user1';

在此处演示.

将来最好将两个用户之间的对话标记为特定对话，然后每个消息都属于一个对话，因此查找用户所涉及的对话以及有关消息的信息变得容易得多与对话有关.无论如何.

It would probably be better, going forward, to flag conversations between two users as a particular conversation, then every message belongs to a conversation, and thus it becomes far easier to find conversations the user is involved in, and info about messages related to the conversation. Anyway.

尝试使用此方法. gh.

Try this one instead. Ugh.

select m.* 
  from messages m 
     left join messages m2 
       on ((m.fromuser = m2.fromuser and m.touser = m2.touser) 
           or (m.fromuser = m2.touser and m.touser = m2.fromuser)) 
         and m.timestamp < m2.timestamp 
  where (m.fromuser = 'user1' or m.touser = 'user1') 
  and m2.id is null;

即使我确实设法解决了该问题，它也可能优于not exists版本.

It's likely to be superior to the not exists version, even if i do manage to fix that one.

此小提琴实际上有效

这篇关于从表中查找最新消息，按用户在mysql中分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！