问题描述
我正在构建嵌套评论功能.我有submissions_comments，它看起来像:
I'm building a nested comments feature. I have submissions_comments which looks like:
+---------------+------------------+------+-----+---------+----------------+
| 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 | |
+---------------+------------------+------+-----+---------+----------------+'
和看起来像这样的用户
+----------------+------------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+------------------------+------+-----+-------------------+-----------------------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| email | varchar(128) | NO | MUL | NULL | |
| username | varchar(23) | NO | | NULL | |
| name | varchar(32) | NO | | NULL | |
| about | varchar(255) | NO | | NULL | |
+----------------+------------------------+------+-----+-------------------+-----------------------------+
并且我正在使用GROUP_CONCAT和LEFT JOIN通过以下查询为我提供所有子注释(在我的submissions_comments表中):
And I'm using GROUP_CONCAT and a LEFT JOIN to give me all child comments (within my submissions_comments table) with this query:
SELECT parent.id, MAX(parent.comment) as pcomm,
GROUP_CONCAT(child.id ORDER BY child.id) as children,
GROUP_CONCAT(child.comment ORDER BY child.id) as childrenComments
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;
这给了我
+----+-------------------------------+----------+--------------------------------------------------------+
| id | pcomm | siblings | siblingComments |
+----+-------------------------------+----------+--------------------------------------------------------+
| 1 | This is a parent | 2,4 | This is a child comment,This is a second child comment |
| 3 | I don't have any children | NULL | NULL |
| 5 | Testing one two three | NULL | NULL |
| 6 | adsdfsasdf | NULL | NULL |
| 7 | asdfadsfdsaf | NULL | NULL |
| 8 | asdfasdsadfsadf | NULL | NULL |
| 9 | asdfsdafsdafdaafds | NULL | NULL |
+----+-------------------------------+----------+--------------------------------------------------------+
我的问题:我正在尝试加入submissions_comments上的users u，以便为每个父注释获取username，email，name，about和孩子的评论.
My problem: I'm trying to join users u on submissions_comments so I can get the username, email, name, about for each parent comment and child comments.
我的查询:
SELECT parent.id, MAX(parent.comment) as pcomm,
parent.username,
GROUP_CONCAT(child.id ORDER BY child.id) as children,
GROUP_CONCAT(child.comment ORDER BY child.id) as childrenComments,
GROUP_CONCAT(child.username ORDER BY child.id) as childrenUsernames
FROM submissions_comments AS parent
LEFT JOIN submissions_comments AS child
ON child.parent_id = parent.id
LEFT JOIN users u on parent.user_id = u.id
GROUP BY parent.id
ORDER BY parent.id;
我想要什么:
+----+-------------------------------+----------+--------------------------------------------------------+-----------------------+
| id | pcomm | children | childrenComments |childrenUsernames |
+----+-------------------------------+----------+--------------------------------------------------------+-----------------------+
| 1 | This is a parent | 2,4 | This is a child comment,This is a second child comment |blahbster, user123 |
| 3 | I don't have any children | NULL | NULL | |
| 5 | Testing one two three | NULL | NULL | |
| 6 | adsdfsasdf | NULL | NULL | |
| 7 | asdfadsfdsaf | NULL | NULL | |
| 8 | asdfasdsadfsadf | NULL | NULL | |
| 9 | asdfsdafsdafdaafds | NULL | NULL | |
+----+-------------------------------+----------+--------------------------------------------------------+-----------------------+
我一直收到以下错误:错误1054(42S22):字段列表"中的未知列"parent.username"和错误1054(42S22):字段列表"中的未知列"child.username".
I keep getting these errors: ERROR 1054 (42S22): Unknown column 'parent.username' in 'field list' and ERROR 1054 (42S22): Unknown column 'child.username' in 'field list'.
推荐答案
如果您同时需要parent和child的用户名，则必须获取它们；它们不在"submissions_comments"表中，而是链接到该表.
If you need usernames for both parent and child, you're going to have to go get them; they aren't in the submissions_comments table, but rather linked to that table.
SELECT parent.id, MAX(parent.comment) as pcomm,
pu.username,
/* display child comments in order of child USER id */
GROUP_CONCAT(cu.id ORDER BY cu.id) as children,
GROUP_CONCAT(child.comment ORDER BY cu.id) as childrenComments,
GROUP_CONCAT(cu.username ORDER BY cu.id) as childrenUsernames
/* handle parent submissions, with user identity */
FROM submissions_comments AS parent
LEFT JOIN users AS pu ON parent.user_id = u.id /* get parents' user info */
/* handle child submissions, with user identity */
LEFT JOIN submissions_comments AS child ON child.parent_id = parent.id
LEFT JOIN users AS cu ON child.user_id = cu.id /* get children's user info */
GROUP BY parent.id, pu.username /* avoid stupid nonstandard MySQL GROUP BY */
ORDER BY parent.id, pu.username
如您所见，您可以对查询进行一些调整以获取所需的内容.
As you can see, there are several tweaks to your query to get what you need.
这篇关于“字段列表"中的未知列"parent.username"在2个表上执行LEFT JOIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！