How to make comment reply query in MYSQL?

Question

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

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.

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?

Thanks

Answer 1

I highly recommend that you restructure your database schema. The main problem is that you are trying to treat comments and replies as the same thing, and they simple aren't the same thing. This is forcing you to make some difficult queries.

Imagine having two tables: [COMMENTS:(id, text)], and replies to comments in another table [REPLIES(id, commentid, text)]. The problem seems much, much easier when thought of in this way.

Answer 2

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

SELECT *
FROM comments
ORDER BY IF(ParentId = 0, Id, ParentId), 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.