如何从表中获取评论和评论的回复

I have a comments table.

我有一张评论表。

I want to get the comments and replies out of the table to display them like this. I am just looking for a query so I can do something like this

我希望得到表格中的评论和回复，以便像这样显示它们。我只是在寻找一个查询，所以我可以做这样的事情

What I Did Query I used

我用过什么查询

SELECT c.post_id, c.id AS comment_id, c.user_id, users.username, c.created, c.comment, r.id AS reply_id, r.parent_comment_id, r.created, r.comment AS reply, r.user_id AS reply_user_id, r_user.username as reply_username FROM (comments c) LEFT JOIN comments r ON c.id = r.parent_comment_id LEFT JOIN users ON c.user_id = users.id LEFT JOIN users as r_user ON r.user_id = r_user.id  WHERE r.id OR c.post_id IS NOT NULL ORDER BY parent_comment_id ASC;

1 个解决方案

#1

Maybe something like this:

也许是这样的：

SELECT
  c.comment,
  IFNULL(r.comment, '') reply
FROM 
  comments c
  LEFT JOIN comments r
    ON c.id = r.parent_comment_id 
WHERE 
  c.parent_comment_id is null