在SQL中创建消息列表 [英] Creating a messages list in SQL

问题描述

我正在尝试创建一个消息列表，例如Facebook.

I'm trying to create a messages list, like Facebook.

1. 仅显示用户对话(历史记录)中的最后一条消息
2. 当我发送show mine时，当我得到答复show show消息时(1行)

数据库数据:

SQL查询

SELECT m.message_id, u.username, m.subject, m.message
     , m.status, UNIX_TIMESTAMP(m.date) as date
  FROM users u
  LEFT JOIN messages m ON m.sender_id = u.id
 WHERE m.message_id in (SELECT max(msg.message_id) 
                          FROM messages msg 
                         WHERE msg.receiver_id = 3 
                         GROUP BY sender_id)
 UNION
SELECT m.message_id, u.username, m.subject, m.message
     , m.status, UNIX_TIMESTAMP(m.date) as date
  FROM users u
  LEFT JOIN messages m ON m.receiver_id = u.id
 WHERE m.message_id in (SELECT max(msg.message_id)  
                          FROM messages msg 
                         WHERE msg.sender_id = 3 
                         GROUP BY receiver_id)
 GROUP BY u.username
 ORDER BY date DESC

我尝试接收所有发送的邮件(我的ID = 3)以及所有发送给我的邮件，并按用户名分组

I try to receive all messages which I send (my id = 3) and all those sent to me and group by username

SQL结果:

Array
(
    [0] => Array
        (
            [message_id] => 10
            [username] => 8888
            [subject] => без темы
            [message] => 555
            [status] => 0
            [date] => 11 August 2012, 2:22
            [user_image] => http://127.0.0.1/auth_system_1/upload_images/65_empty.jpg
        )

    [1] => Array
        (
            [message_id] => 7
            [username] => 8888
            [subject] => hi
            [message] => 333
            [status] => 0
            [date] => 11 August 2012, 2:15
            [user_image] => http://127.0.0.1/auth_system_1/upload_images/65_empty.jpg
        )

    [2] => Array
        (
            [message_id] => 4
            [username] => 6666
            [subject] => Тема
            [message] => 2
            [status] => 0
            [date] => 11 August 2012, 2:02
            [user_image] => http://127.0.0.1/auth_system_1/upload_images/65_empty.jpg
        )

    [3] => Array
        (
            [message_id] => 1
            [username] => fffffffff
            [subject] => privet
            [message] => tttt
            [status] => 0
            [date] => 11 August 2012, 1:38
            [user_image] => http://127.0.0.1/auth_system_1/upload_images/65_empty.jpg
        )

)

如您所见，GROUP BY用户名无效.它显示3-> 7和7-> 3，但是7-> 3是答案和最后一条消息.我不知道为什么小组不起作用.也许您可以通过更简单的方法来帮助我解决此问题?

As you can see GROUP BY username do not work. It shows 3->7 and 7->3 but 7->3 was an answer and last message. I do not know why group does not work. Maybe you can help me with more easier way to solve this problem?

因此，SQL必须具有"sender_id = 3"，"receiver_id = 3"，并且从我的表中得出的数据结果必须为

So, the SQL must have "sender_id = 3" , "receiver_id = 3" and from my table data result must be

• message_id-> 1
• message_id-> 4
• message_id-> 10

推荐答案

我认为您的查询产生了正确"的结果，就好像您希望在某些对话中看到最后一条消息一样，您应该进行分组通过conversation_id.我在架构中看不到这个字段.

I think your query is producing the "right" results, as if you'd like to see the last message in some of the conversations, you should really group by the conversation_id. I don't see this field you in schema though.

如果您执行WHERE sender_id = 3 GROUP BY receiver_id，则是正确的，该查询将向您返回消息1和7，因为这些消息已发送给不同的人，因此在您的设计中它们是不同的对话.

If you do WHERE sender_id = 3 GROUP BY receiver_id, then it is correct, that query returns you messages 1 and 7, 'cos those messages had been sent to different people, thus in your design they're different conversations.

如果您只想查看您发送的最后一条消息，只需删除UNION第二部分中的GROUP BY.否则，请考虑重新设计架构.

If you want to see only the very last message sent by you in general, just remove GROUP BY in the second part of your UNION. Otherwise, consider re-designing your schema.

尝试以下查询:

SELECT m.message_id, u.username, m.subject, m.message,
       m.status, UNIX_TIMESTAMP(m.date) as `date`
  FROM users u
  LEFT JOIN messages m ON m.sender_id = u.id
 WHERE m.message_id IN (
    SELECT max(message_id) 
      FROM messages
     WHERE receiver_id = 3 OR sender_id = 3
     GROUP BY least(sender_id,receiver_id), 
              greatest(sender_id,receiver_id)
    );

一些注意事项:

1. UNION不再需要；
2. 这种方法会将两方之间的所有电子邮件视为单个对话，但并非总是如此.您可能需要重新设计这种方法；
3. 对列的名称或别名使用保留字(例如date)是一种不良样式，请尝试避免这种情况.或使用反引号(如果您确实使用过的话).

1. UNION is not needed anymore;
2. This approach will treat all e-mails between 2 parties as a single conversation, which is not always true. You might want to re-design this approach;
3. It is a bad style to use reserved words (like date) for columns' names or aliases, try to avoid this. Or use backticks if you do use them.

这篇关于在SQL中创建消息列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！