Message and reply massage functionality

Question

We've a table called message.

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`)
) 

Need to display the messages and message reply to the user. Entries in the table will like this,

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

Here is the flow, lets assume 2=>A, 3 =>B, 4 =>C, 5=> D,

• A send a message to B
• B reply to that message
• B send again one more reply by adding new recipient C
• C reply to that thread again by adding new recipient D
• All users part of this thread, should able to read full message thread.
• A,B,C and D can see the all (1,2,3,4,5) messages if they login except 6th
• 6th message only C and D can see and it is a different thread

Two queries I'm using now,

1. One for to list all messages.
2. Second is for to see the details for that message(when user click on that will show all thread related to that message).

By using single query I need to show the all threads to the login user. Please help some one to select query for this.

Answer 1

Your storage type is called Adjacency list, i.e. just store immediate parent id in parent_id column.

To query node's children:

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)

If you would like a flat structure, you can do the following query:

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 has serious drawbacks: it's hard to query deeply nested trees (we're querying only immediate children of message #1 here).

Please, take a look at linked question and also this excellent presentation by Bill Karwin for other options.

Answer 2

Make the default for parent_id NULL. Gets threads user is allowed to view, replace <thisuserid> with user id

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

Get whole thread, including duplicates when sending to many recipients since i cant think of a fool proof way to filter them out as they are stored as separate messages. replace <thisuserid> with user id

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

Although, i would totally separate the recipient from the message itself, not only to make querying the whole chain easier, but also to save space. They way you do it now, every new recipient of a message increases the storage required by an amount equal to the size of the message, which can get out of hand very quickly.

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 threads user is allowed to view

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
);

Get whole thread

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