MySQL GROUP BY / ORDER BY问题与平面消息表/线程 [英] MySQL GROUP BY / ORDER BY issue with flat messages table / threads

问题描述

好吧，我正在尝试基于类似的东西，但没有完全弄明白： GROUP BY和ORDER BY

基本上，希望查询在当前登录用户的每个线程中查找最新消息以及任何其他用户，但通过一个扁平（非线程）的消息表：

 消息{
 id，
 from_uid，
 to_uid，
 message_text，
 time_added 
} 
  

假设当前用户的uid为'1'，并且每个'线程'中的最新消息可能来自该用户的，或者来自 >那个用户（对方总是用thread_recipient表示）：

  SELECT a。*，thread_recipient 
 FROM messages a 
 JOIN（SELECT IF（from_uid ='1'，to_uid，from_uid）AS thread_recipient，
 MAX（time_added）AS recency 
 FR OM消息
 WHERE（from_uid ='1'或to_uid ='1'）
 GROUP BY thread_recipient）b ON thread_recipient =（IF（a.from_uid ='1'，a.to_uid，a.from_uid ））
 AND b.recency = a.time_added 
 ORDER BY a.time_added DESC 
  

但是我担心这样做可能无法正常工作，并且可能同时发送的邮件可能最终会被错误的用户返回？

是我的WHERE条件错位？

任何智慧都不胜感激。

解决方案

idea：使用以下两个查询的UNION，然后从结果中获得
的最大日期。

  SELECT id ，to_uid AS other_party，time_added FROM messages WHERE from_uid ='1'
 
 SELECT id，from_uid AS other_party，time_added FROM messages WHERE to_uid ='1'
  

执行以下操作时：

  SELE CT MAX（time_added），other_party 
 FROM（SELECT id，to_uid AS other_party，time_added FROM messages WHERE from_uid ='1'
 UNION 
 SELECT id，from_uid AS other_party，time_added FROM messages WHERE to_uid ='1'
）MyMessages 
 GROUP BY other_party 
  

您将获得最近的时间与发送给用户'1'所对应的每个人
的消息相关联。然后，您可以将
的结果加入到原始邮件表中，以获取所需内容：

  SELECT邮件。 * 
 FROM（SELECT MAX（time_added）AS MaxTime，other_party 
 FROM（SELECT id，to_uid AS other_party，time_added FROM messages WHERE from_uid ='1'
 UNION 
 SELECT id， from_uid AS other_party，time_added FROM messages WHERE to_uid ='1'
）MyMessages 
 GROUP BY other_party 
）
 JOIN消息
 ON（Messages.time_added = MyMessages.MaxTime AND 
（Messages.to_uid = MyMessages.other_party AND Messages.from_uid ='1'OR 
 Messages.from_uid = MyMessages.other_party AND Messages.to_uid ='1'）
）
  

Ok, I'm trying to base something similar off of this, but not quite getting it nailed: GROUP BY and ORDER BY

Basically, wanting a query to find the latest messages in each 'thread' between the current logged-in user and any other users, but via a flat (non-'threaded') table of messages:

messages {
  id,
  from_uid,
  to_uid,
  message_text,
  time_added
}

Assuming the current user's uid is '1', and the latest message in each 'thread' could either be from that user, or to that user (the other party always denoted by thread_recipient):

SELECT a.*,thread_recipient
  FROM messages a
  JOIN (SELECT IF(from_uid = '1',to_uid,from_uid) AS thread_recipient,
               MAX(time_added) AS recency
          FROM messages 
         WHERE (from_uid = '1' OR to_uid = '1')
      GROUP BY thread_recipient) b ON thread_recipient = (IF(a.from_uid = '1',a.to_uid,a.from_uid))
                             AND b.recency = a.time_added
 ORDER BY a.time_added DESC

But I fear this ain't gonna work right, and maybe messages sent at the same time might end up being returned for the wrong user?

Is my WHERE condition misplaced?

Any wisdom much appreciated.

解决方案

Here's an idea: take the UNION of the following two queries, then get the maximum dates from the result.

SELECT id,to_uid AS other_party,time_added FROM messages WHERE from_uid = '1'

SELECT id,from_uid AS other_party,time_added FROM messages WHERE to_uid = '1'

When you do the following:

SELECT MAX(time_added),other_party
FROM (SELECT id,to_uid AS other_party,time_added FROM messages WHERE from_uid = '1'
      UNION
      SELECT id,from_uid AS other_party,time_added FROM messages WHERE to_uid = '1'
     ) MyMessages
GROUP BY other_party

You will get the most recent time associated with a message sent to each person that user '1' is corresponding with. Then you can join the results of that to the original messages table to get what you want:

SELECT Messages.*
FROM (SELECT MAX(time_added) AS MaxTime,other_party
      FROM (SELECT id,to_uid AS other_party,time_added FROM messages WHERE from_uid = '1'
            UNION
            SELECT id,from_uid AS other_party,time_added FROM messages WHERE to_uid = '1'
           ) MyMessages
      GROUP BY other_party
     )
     JOIN Messages
     ON (Messages.time_added = MyMessages.MaxTime AND
          (Messages.to_uid = MyMessages.other_party AND Messages.from_uid = '1' OR
           Messages.from_uid = MyMessages.other_party AND Messages.to_uid = '1')
        )

这篇关于MySQL GROUP BY / ORDER BY问题与平面消息表/线程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！