消息和回复按摩功能

Question

我们有一张名为“信息”的桌子。

CREATE TABLE IF NOT EXISTS `message` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`from_user_id` int(11) NOT NULL,
`to_user_id` int(11) NOT NULL,
`content` text NOT NULL,
`club_id` int(11) NOT NULL,
`read_flag` int(11) NOT NULL DEFAULT '0',
`parent_id` int(11) NOT NULL,      
`status` tinyint(1) DEFAULT NULL,
`create_user_id` int(11) NOT NULL,
`update_user_id` int(11) NOT NULL,
`create_dt_tm` datetime NOT NULL,
`update_dt_tm` datetime NOT NULL,
`delete_flag` tinyint(1) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`)
) 

需要向用户显示消息和消息回复。表中的条目会喜欢这个，

id | from_user_id | to_user_id | content | parent_id
1  | 2            | 3          | hai     | 0
2  | 3            | 2          | hi      | 1
3  | 3            | 2          | hwru    | 1
4  | 3            | 4          | hwru    | 1
5  | 4            | 5          | u added | 1
6  | 4            | 5          | new msg | 0

下面是流程，假设2=>A，3 =>B，4 =>C，5=> D，

• A向B发送信息
• B回复该电文
• 再发送一次回复，添加新的收件人C
• C通过添加新的收件人D再次回复该线程
• 这个线程的所有用户都应该能够读取完整的消息线程。
• A、B、C和D可以看到所有(1,2,3,4,5)消息，但第6条除外。
• 第6条消息只有C和D才能看到，这是一个不同的线程。

我现在用的两个查询，

1. 一个用来列出所有信息。
2. 第二，用于查看该消息的详细信息(当用户单击该消息时，将显示与该消息相关的所有线程)。

通过使用单个查询，我需要向登录用户显示所有线程。请帮助一些人选择这个查询。

Answer 1

您的存储类型称为Adjacency list，即只在parent_id列中存储直接父id。

若要查询节点的子节点：

mysql> SELECT * FROM message m1 INNER JOIN message m2 ON m2.parent_id = m1.id  WHERE m1.id = 1;
+----+--------------+------------+---------+-----------+----+--------------+------------+---------+-----------+
| id | from_user_id | to_user_id | content | parent_id | id | from_user_id | to_user_id | content | parent_id |
+----+--------------+------------+---------+-----------+----+--------------+------------+---------+-----------+
|  1 |            2 |          3 | hai     |         0 |  2 |            3 |          2 | hi      |         1 |
|  1 |            2 |          3 | hai     |         0 |  3 |            3 |          2 | hwru    |         1 |
|  1 |            2 |          3 | hai     |         0 |  4 |            3 |          4 | hwru    |         1 |
|  1 |            2 |          3 | hai     |         0 |  5 |            4 |          5 | u added |         1 |
+----+--------------+------------+---------+-----------+----+--------------+------------+---------+-----------+
4 rows in set (0.00 sec)

如果希望使用平面结构，可以执行以下查询：

mysql> select * from message m WHERE id = 1 OR parent_id = 1;
+----+--------------+------------+---------+-----------+
| id | from_user_id | to_user_id | content | parent_id |
+----+--------------+------------+---------+-----------+
|  1 |            2 |          3 | hai     |         0 |
|  2 |            3 |          2 | hi      |         1 |
|  3 |            3 |          2 | hwru    |         1 |
|  4 |            3 |          4 | hwru    |         1 |
|  5 |            4 |          5 | u added |         1 |
+----+--------------+------------+---------+-----------+
5 rows in set (0.00 sec)

Adjacency list有严重的缺点:很难查询深度嵌套的树(这里只查询消息#1的直接子级)。

请看一下关联问题，还有比尔·卡温的出色的演讲，还有其他选项。

Answer 2

使parent_id的默认值为空。获取线程用户可以查看，用用户id替换。

SELECT DISTINCT(COALESCE(parent_id, id)) thread_id FROM message m WHERE from_user_id = <thisuserid> OR to_user_id = <thisuserid>

获取整个线程，包括发送给许多收件人时的副本，因为我想不出一种愚蠢的方法来过滤掉它们，因为它们是作为单独的消息存储的。用用户id替换

SELECT * from message m WHERE id = <threadid> OR parent_id = <threadid>

虽然，我会将收件人与消息本身完全分开，这不仅使查询整个链变得更容易，而且还节省了空间。他们现在就是这样做的，每一个新的邮件接收者都会增加与消息大小相等的存储量，这样很快就会失去控制。

CREATE TABLE IF NOT EXISTS `message` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`from_user_id` int(11) NOT NULL,
`content` text NOT NULL,
`parent_id` int(11),      
 PRIMARY KEY (`id`)
); 

CREATE TABLE IF NOT EXISTS `message_to` (
`message_id` int(11) NOT NULL,
`recipient_id` int(11) NOT NULL,
`read_flag` int(11) NOT NULL DEFAULT '0',
`status` tinyint(1) DEFAULT NULL,
`delete_flag` tinyint(1) NOT NULL DEFAULT '0',
UNIQUE KEY (`message_id`, `recipient_id`)
);


INSERT INTO message VALUES (1, 2, 'hai', null), (2, 3, 'hi', 1), (3, 3, 'hwru', 1), (4, 4, 'u added', 1), (5, 4, 'new msg', null);
INSERT INTO message_to (`message_id`, `recipient_id`) VALUES (1,3), (2,2), (3,2), (3,4), (4,5), (5,5);

Get线程用户可以查看

SET @user := 2;
SELECT DISTINCT(COALESCE(parent_id, id)) thread_id FROM message m WHERE id IN (
    SELECT message_id as id FROM message_to WHERE recipient_id = @user
union
    SELECT id from message where from_user_id = @user
);

得到整条线

SELECT * FROM message m WHERE m.id = 1 OR m.parent_id = 1