I have troubles trying to sort comments and replies (unlimited nesting levels) in my laravel project.
My table comments
has similar structure (simplified):
id
| body
| user_id
| parent_id
| commentable_id
| created_at
The (root) comments have NULL value for parent_id
.
The amount of the comments and replies for a single page (commentable_id
) could be more than 500.
I want to be able to sort the comments created_at
DESC, but the tree structure of the replies should be retained.
Till now I have this query:
WITH RECURSIVE child_comments (id, body, parent_id, created_at, path, depth) AS
(
SELECT id, body, parent_id, created_at,
CAST(id AS CHAR(200)),
0 as depth
FROM comments
WHERE
commentable_id = 150
AND parent_id is NULL
UNION ALL
SELECT c.id, c.body, c.parent_id, c.created_at,
CONCAT(cc.path, ', ', c.id),
cc.depth + 1
FROM child_comments cc
JOIN comments c ON c.parent_id =cc.id
WHERE c.commentable_id = 150
) SELECT * from child_comments
ORDER BY
CASE WHEN parent_id != 0 THEN path END,
CASE WHEN parent_id is NULL THEN `created_at` END DESC;
The result is this:
+——+——————+———–+———————+—————+——-+
| id | body | parent_id | created_at | path | depth |
+——+——————+———–+———————+—————+——-+
| 7 | coment 3 | NULL | 2024-05-09 12:13:07 | 7 | 0 |
| 2 | coment 2 | NULL | 2024-05-02 21:07:29 | 2 | 0 |
| 1 | coment 1 | NULL | 2024-05-02 17:28:42 | 1 | 0 |
| 3 | coment 1_1 | 1 | 2024-05-07 23:02:50 | 1, 3 | 1 |
| 5 | coment 1_1_1 | 3 | 2024-05-07 23:03:02 | 1, 3, 5 | 2 |
| 6 | coment 1_1_1_1 | 5 | 2024-05-07 23:05:26 | 1, 3, 5, 6 | 3 |
| 9 | coment 1_1_1_1_1 | 6 | 2024-05-09 12:14:06 | 1, 3, 5, 6, 9 | 4 |
| 4 | coment 1_2 | 1 | 2024-05-07 23:02:57 | 1, 4 | 1 |
| 8 | coment 2_1 | 2 | 2024-05-09 12:13:35 | 2, 8 | 1 |
+——+——————+———–+———————+—————+——-+
But I want such result:
+—-+——————-+———–+———————+—————+——-+
| id | body | parent_id | created_at | path | depth |
+—-+——————-+———–+———————+—————+——-+
| 7 | comment 3 | NULL | 2024-05-09 12:13:07 | 7 | 0 |
| 2 | comment 2 | NULL | 2024-05-02 21:07:29 | 2 | 0 |
| 8 | comment 2_1 | 2 | 2024-05-09 12:13:35 | 2, 8 | 1 |
| 1 | comment 1 | NULL | 2024-05-02 17:28:42 | 1 | 0 |
| 3 | comment 1_1 | 1 | 2024-05-07 23:02:50 | 1, 3 | 1 |
| 5 | comment 1_1_1 | 3 | 2024-05-07 23:03:02 | 1, 3, 5 | 2 |
| 6 | comment 1_1_1_1 | 5 | 2024-05-07 23:05:26 | 1, 3, 5, 6 | 3 |
| 9 | comment 1_1_1_1_1 | 6 | 2024-05-09 12:14:06 | 1, 3, 5, 6, 9 | 4 |
| 4 | comment 1_2 | 1 | 2024-05-07 23:02:57 | 1, 4 | 1 |
+—-+——————-+———–+———————+—————+——-+
Any suggestions how to do it?
Ridderspoor is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.