mysql - 将嵌套的子注释与 MySQL 组合

Question

所以我在这里接受了答案Select Parent and Children With MySQL对于我的 submissions_comments 架构，它看起来像:

+---------------+------------------+------+-----+---------+----------------+
| Field         | Type             | Null | Key | Default | Extra          |
+---------------+------------------+------+-----+---------+----------------+
| id            | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| user_id       | int(10) unsigned | NO   | MUL | NULL    |                |
| submission_id | int(11)          | NO   | MUL | NULL    |                |
| comment       | text             | NO   |     | NULL    |                |
| parent_id     | int(10) unsigned | YES  | MUL | NULL    |                |
| created       | datetime         | NO   | MUL | NULL    |                |
| created_ip    | int(11)          | NO   |     | NULL    |                |
| helpful_count | int(11)          | NO   | MUL | NULL    |                |
| deleted       | tinyint(4)       | NO   | MUL | 0       |                |
+---------------+------------------+------+-----+---------+----------------+'

作为

  SELECT *
    FROM submissions_comments AS parent
         LEFT JOIN submissions_comments AS child 
         ON child.parent_id = parent.id
   WHERE parent.parent_id IS NULL
ORDER BY parent.id, child.id;

最后我得到了以下结果:

+----+---------+---------------+-------------------------------+-----------+---------------------+------------+---------------+---------+------+---------+---------------+--------------------------------+-----------+---------------------+------------+---------------+---------+
| id | user_id | submission_id | comment                       | parent_id | created             | created_ip | helpful_count | deleted | id   | user_id | submission_id | comment                        | parent_id | created             | created_ip | helpful_count | deleted |
+----+---------+---------------+-------------------------------+-----------+---------------------+------------+---------------+---------+------+---------+---------------+--------------------------------+-----------+---------------------+------------+---------------+---------+
|  1 |      15 |            23 | This is a parent              |      NULL | 2014-02-19 01:41:39 |     127001 |             0 |       0 |    2 |      15 |            23 | This is a child comment        |         1 | 2014-02-19 01:41:43 |     127001 |             0 |       0 |
|  1 |      15 |            23 | This is a parent              |      NULL | 2014-02-19 01:41:39 |     127001 |             0 |       0 |    4 |      15 |            23 | This is a second child comment |         1 | 2014-02-19 02:01:29 |     127001 |             0 |       0 |
|  3 |      15 |            23 | I don't have any children |      NULL | 2014-02-19 01:43:30 |     127001 |             0 |       0 | NULL |    NULL |          NULL | NULL                           |      NULL | NULL                |       NULL |          NULL |    NULL |
+----+---------+---------------+-------------------------------+-----------+---------------------+------------+---------------+---------+------+---------+---------------+--------------------------------+-----------+---------------------+------------+---------------+---------+

如您所见，结果中的前两行包含合并了子评论的父评论。 MySQL 是否有一种方法可以将所有子评论干净地嵌套在一个返回的父评论中，或者我是否需要在返回的结果对象上使用下划线的 _.pluck 方法？

Answer 1

你是这个意思吗？

SELECT parent.id, MAX(parent.comment) as pcomm,
       GROUP_CONCAT(child.id ORDER BY child.id) as siblings,
       GROUP_CONCAT(child.comment ORDER BY child.id) as siblingComments
FROM   submissions_comments AS parent
 LEFT  JOIN submissions_comments AS child
   ON  child.parent_id = parent.id
WHERE  parent.parent_id IS NULL
GROUP  BY parent.id
ORDER  BY parent.id;

我假设“嵌套”只是意味着您希望将兄弟结果以某种方式组合在一起。