按消息分组 MySQL [英] GROUP BY messages MySQL

问题描述

我开始为我的网站开发聊天应用程序.

I started developing chat application for my website.

在进入后端之前，我首先做了一些 javascript 部分.现在刚刚创建的数据库结构:

First I did some javascript part, before I got to backend. And Now just created database structure:

CREATE TABLE IF NOT EXISTS `wp_bp_my_chat` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `from` varchar(255) NOT NULL DEFAULT '',
  `to` varchar(255) NOT NULL DEFAULT '',
  `message` text NOT NULL,
  `sent` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `recd` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `to` (`to`),
  KEY `from` (`from`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

现在，有了这个数据库，我想请求查看按from"或To"分组的所有消息

Now, having this databse, I want to make a request to see all messages grouped by "from" OR "To"

将其视为 Facebook 消息，当您转到实际页面时，会出现一个左侧边栏，其中包含按对话分组的消息.

Think of it as facebook messages, when you go to actual page, there is a left sidebar with messages grouped by conversation.

输出应该是这样的:

• user_1"和user_2"之间的对话(未读)2 小时前

• conversation between "user_1" and "user_2" (unread) 2 hours ago

user_1"和user_3"之间的对话(未读)3 小时前

conversation between "user_1" and "user_3" (unread) 3 hours ago

5 小时前user_1"和user_5"之间的对话

converstation between "user_1" and "user_5" 5 hours ago

所以我的消息像对话一样分组.我可能有来自 user_2 的 10 条消息，但它应该显示为一条(以及上一条的信息)

so my messages are grouped like conversations. I might have 10 message from user_2 but it should be displayed as one (and info from last one)

有什么想法我下一步该怎么做?由于我还没有做过任何 php 方面的工作，您甚至可以建议更改数据库以适应您的解决方案.

Any Ideas how I go next? As I have not done any php side yet You can even suggest changing database to adjast for your solution.

谢谢.

推荐答案

我假设你会为一个人 ('user_1') 运行这个程序来处理他们的对话，这意味着他们可以是 from 或 to.我还假设它们是 from 还是 to 没有区别，而是由对话中的另一个人分组.如果是这样，试试这个.(你应该在 SQLFiddle 中放一些样本数据进行测试)

I assume you would run this for one person ('user_1') for their conversations, which means they can be either the from or the to. I also assume that it make no difference if they are the from or the to, but to group by the other person in the conversation. If so, try this. (You should put some sample data in SQLFiddle for testing)

SELECT MostRecent.MainPerson AS MainPerson
  , MostRecent.OtherPerson AS OtherPerson
  , MostRecent.Sent AS Sent
  , IF(wp_bp_my_chat.recd = 0, 'Unread','Read') AS Status
FROM wp_bp_my_chat
JOIN (
    SELECT 'user_1' AS MainPerson
       , IF(msgs.`from` = 'user_1',msgs.to, msgs.`from`) AS OtherPerson
       , MAX(msgs.sent) AS sent
    FROM wp_bp_my_chat AS msgs
    WHERE msgs.`from` = 'user_1' OR msgs.`to` = 'users_1'
    GROUP BY MainPerson, OtherPerson) AS MostRecent
  ON (wp_bp_my_chat.`from` = MostRecent.MainPerson OR wp_bp_my_chat.`to` = MostRecent.MainPerson)
    AND (wp_bp_my_chat.`from` = MostRecent.OtherPerson OR wp_bp_my_chat.`to` = MostRecent.OtherPerson)
    AND MostRecent.sent = wp_bp_my_chat.sent
ORDER BY sent DESC

这篇关于按消息分组 MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！