Laravel项目中MySQL无限层级评论及回复排序问题

问题背景 #

在Laravel项目中处理无限嵌套的评论及回复排序时遇到问题，需求是按created_at降序排列评论，同时保留回复的树形结构。单页对应commentable_id的评论数量可能超过500条。

表结构（简化） #

comments表字段：
id | body | user_id | parent_id | commentable_id | created_at
根评论的parent_id为NULL。

当前使用的查询语句 #

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;

当前查询结果 #

+------+------------------+-----------+---------------------+---------------+-------+
| 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 |
+------+------------------+-----------+---------------------+---------------+-------+

期望结果 #

+----+-------------------+-----------+---------------------+---------------+-------+
| 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 |
+----+-------------------+-----------+---------------------+---------------+-------+

解决方案 #

当前查询的排序逻辑存在缺陷：根评论按created_at降序排列，但子评论未紧跟对应父评论，而是所有根评论排完后才集中显示子评论。要实现根评论降序、子评论嵌套跟随父评论的需求，需调整递归CTE的排序依据字段，并修改排序规则。

修改后的查询语句（保留原path格式） #

WITH RECURSIVE child_comments (id, body, parent_id, created_at, sort_path, depth, root_created) AS
(   
    SELECT id, body, parent_id, created_at,
        CONCAT(LPAD(CAST(UNIX_TIMESTAMP(created_at) AS CHAR), 10, '0'), ',', id) AS sort_path,
        0 as depth,
        UNIX_TIMESTAMP(created_at) as root_created
    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.sort_path, ',', LPAD(CAST(UNIX_TIMESTAMP(c.created_at) AS CHAR), 10, '0'), ',', c.id),
        cc.depth + 1,
        cc.root_created
    FROM child_comments cc 
    JOIN comments c ON c.parent_id = cc.id
    WHERE c.commentable_id = 150
) 
SELECT 
    id, 
    body, 
    parent_id, 
    created_at,
    -- 还原原path格式（仅用于展示）
    SUBSTRING_INDEX(GROUP_CONCAT(SUBSTRING_INDEX(sort_path, ',', -1) ORDER BY depth), ',', depth+1) AS path,
    depth
from child_comments
-- 先按根评论时间降序，再按sort_path降序确保子评论跟随父评论
ORDER BY root_created DESC, sort_path DESC;

简化方案（无需保留原path格式，性能更优） #

如果不需要展示原path字段，可直接用包含时间戳的排序路径实现需求，适合大数量评论场景：

WITH RECURSIVE child_comments (id, body, parent_id, created_at, sort_path, depth) AS
(   
    SELECT id, body, parent_id, created_at,
        CONCAT(LPAD(CAST(UNIX_TIMESTAMP(created_at) AS CHAR), 10, '0'), '-', id) AS sort_path,
        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.sort_path, '-', LPAD(CAST(UNIX_TIMESTAMP(c.created_at) AS CHAR), 10, '0'), '-', c.id),
        cc.depth + 1 
    FROM child_comments cc 
    JOIN comments c ON c.parent_id = cc.id
    WHERE c.commentable_id = 150
) 
SELECT id, body, parent_id, created_at, depth
from child_comments
ORDER BY sort_path DESC;

逻辑说明 #

1. 排序路径设计：将评论的时间戳（补零为固定长度）与id拼接成sort_path，确保排序时能按时间降序排列，同时让子评论的路径继承父评论的路径前缀，自然实现嵌套跟随。
2. 排序规则：直接按sort_path降序，即可让根评论按时间从新到旧排列，子评论自动紧跟对应父评论，且子评论内部也按时间降序排列。

内容的提问来源于stack exchange，提问作者Ridderspoor