如何在MYSQL中发表评论回复查询？ [英] How to make comment reply query in MYSQL?

问题描述

我有评论回复（只有一个级别）功能。

I am having comment reply (only till one level) functionality. All comments can have as many as replies but no replies can have their further replies.

所以我的数据库表结构如下

So my database table structure is like below

Id    ParentId    Comment
1     0           this is come sample comment text
2     0           this is come sample comment text
3     0           this is come sample comment text
4     1           this is come sample comment text
5     0           this is come sample comment text
6     3           this is come sample comment text
7     1           this is come sample comment text

在上述结构中，commentid，1（有2个回复）和3回复。所以要获取评论和他们的回复，一个简单的方法是首先获取所有的ParentId为0的评论，然后运行一个while循环获取该特定评论的所有回复。但是，如果我在一个特定的记录上有大约200条评论，那似乎是运行了数百个查询。

In the above structures, commentid, 1 (has 2 replies) and 3 (1 reply) has replies. So to fetch the comments and their replies, one simple method is first I fetch all the comments having ParentId as 0 and then by running a while loop fetch all the replies of that particular commentId. But that seems to be running hundreds of queries if I'll have around 200 comments on a particular record.

所以我想做一个查询，

So I want to make a query which will fetch Comments with their replies sequentially as following;

Id    ParentId    Comment
1     0           this is come sample comment text
4     1           this is come sample comment text
7     1           this is come sample comment text
2     0           this is come sample comment text
3     0           this is come sample comment text
6     3           this is come sample comment text    
5     0           this is come sample comment text

日期列在我的注释表中，如果任何人想要使用这个与评论查询。

I also have a comment date column in my comment table, if anyone wants to use this with comment query.

所以最后我想通过使用一个单一的mysql查询获取所有的评论和他们的回复。

So finally I want to fetch all the comments and their replies by using one single mysql query. Please tell me how I can do that?

感谢

推荐答案

您可以在ORDER BY中使用表达式。尝试此操作：

You can use an expression in an ORDER BY. Try this:

SELECT *
FROM comments
ORDER BY IF(ParentId = 0, Id, ParentId), Id

这将首先按Id进行排序，如果ParentId = 0，第二个排序标准是Id，以确保按顺序返回回复。

This will first sort by Id, if ParentId = 0, or by ParentId otherwise. The second sort criterion is the Id, to assure that replies are returned in order.

这篇关于如何在MYSQL中发表评论回复查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！