本文介绍了“字段列表"中的未知列"parent.username"在2个表上执行LEFT JOIN的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我正在构建嵌套评论功能.我有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_CONCATLEFT 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,以便为每个父注释获取usernameemailnameabout和孩子的评论.

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'.

推荐答案

如果您同时需要parentchild的用户名,则必须获取它们;它们不在"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的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

11-03 08:09