因此,我在这里接受了一个可接受的答案,为我的架构选择“ 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
在返回的结果对象上使用下划线方法?
你是这个意思吗?
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;
我假设通过“嵌套”,您只是意味着您希望以某种方式将同级结果分组在一起。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句