How to Make Comment Reply Query in MySQL

How to make comment reply query in MYSQL?

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.

How to create a MySQL query that returns the replies to a given user's comments

If you just want to retrieve the first level replies (ignoring those from the initial user), you can JOIN comments to itself to find the replies, filtering out those with the same userid:

SELECT rep.*
FROM comments rep
JOIN comments cmt
WHERE rep.commid = cmt.commentid
  AND cmt.userid = 1
  AND rep.userid != 1

Output (for your sample data):

commentid   postid  commid  username    userid  commtext
53          100     52      user2       2       second

Demo on db-fiddle

Comment reply query (in reverse order)

I think you are looking for this:

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

This will sort all parents in ASC order, then all comments in DESC order, leaving each parent at the top. Please see fiddle here.

Retrieving comments and reply with limits

I assume that your ID columns are of type INT. If not, you might have to use variables to count the replies per post, described here.

So try this

SELECT 'POST' AS type, p1.post_id, p1.comment, p1.post_user, p1.post_date, '-' AS reply_id
 FROM post p1
 LIMIT 10

UNION

SELECT 'REPLY' AS type, pr.parent_id AS post_id, 
 '-' AS comment, pr.reply_user AS post_user, pr.reply_date AS post_date, pr.reply_id
 FROM post_reply pr
 JOIN (
     SELECT p2.post_id 
     FROM post p2 
     LIMIT 10
 ) AS tmpPost ON pr.parent_id = tmpPost.post_id
 JOIN (
     SELECT pr.reply_id, COUNT(*) AS row_number
     FROM post_reply pr 
     JOIN post_reply pr2 ON pr.parent_id = pr2.parent_id AND pr.reply_id >= pr2.reply_id
     GROUP BY pr.reply_id
 ) AS tmpPostRN ON tmpPostRN.reply_id = pr.reply_id
 WHERE tmpPostRN.row_number <= 2

ORDER BY post_id ASC, type ASC, reply_id ASC

The Result will be something like

+-------+---------+-----------+-----------------+------------+----------+
| type  | post_id | comment   | post_user       | post_date  | reply_id |
+-------+---------+-----------+-----------------+------------+----------+
| POST  |       1 | comment1  | user1           | 0000-00-00 | -1       |
| REPLY |       1 | -         | post 1 reply1   | 0000-00-00 | 1        |
| REPLY |       1 | -         | post 1 reply 2  | 0000-00-00 | 2        |
| POST  |       2 | comment2  | user2           | 0000-00-00 | -1       |
| POST  |       3 | comment3  | user3           | 0000-00-00 | -1       |
| REPLY |       3 | -         | post 3 reply 1  | 0000-00-00 | 3        |
| REPLY |       3 | -         | post 3 reply 2  | 0000-00-00 | 4        |
| POST  |       4 | comment4  | user 4          | 0000-00-00 | -1       |
| POST  |       5 | comment5  | user 5          | 0000-00-00 | -1       |
| POST  |       6 | comment6  | user6           | 0000-00-00 | -1       |
| POST  |       7 | comment7  | user7           | 0000-00-00 | -1       |
| POST  |       8 | comment8  | user8           | 0000-00-00 | -1       |
| POST  |       9 | comment9  | user9           | 0000-00-00 | -1       |
| POST  |      10 | comment10 | user10          | 0000-00-00 | -1       |
| REPLY |      10 | -         | post 10 reply 1 | 0000-00-00 | 5        |
| REPLY |      10 | -         | post 10 reply 2 | 0000-00-00 | 6        |
+-------+---------+-----------+-----------------+------------+----------+

Instead of '-' AS comment in the second query you can insert your comment column in your post_reply table (provided that you have one). You can also set your reply_id for posts to a default value of your choice (rather than -1).

mysql structure for comments and comment replies

I decided to add the parent_id column in the database and instead of left joining the replies I just selected all the comments at once to later on sort the comments and replies with server-side code, heres the query:

SELECT c.*, u.username, u.photo
FROM (comments c)
JOIN users u ON c.user_id = u.id
WHERE c.topic_id = 9
ORDER BY c.id ASC

Now I pass the query result to the below function so that every reply will be added as an array inside the comment array, so basically it returns a multidimensional array.

function sort_comments($ar)
{
    $comments = array();
    foreach($ar as $item)
    {
        if(is_null($item['parent_id'])) $comments[] = $item;
        else 
        {
            $parent_array = array_search_key($item['parent_id'],$comments,'id');
            if($parent_array !== false) $comments[$parent_array]['replies'][] = $item;
        }
    }
    return $comments;
}

Retrieve number of replies to a comment

This looks like a good spot for a subquery, or a lateral join:

select c.*, c1.no_replies
from comments c
cross join lateral (
    select count(*) no_replies
    from comments c1
    where c1.parentid = c.commentid
) c1 
where c.postid = :givenpostid

Side note - the query you wanted to write probably is:

SELECT c0.*, COALESCE(c1.numReplies, 0) as numReplies
FROM comments c0
LEFT JOIN (
    SELECT parentID, COUNT(*) as numReplies
    FROM comments
    GROUP BY parentID
) c1 on c0.commentID = c1.parentID
WHERE c0.postID = :givenPostID

The subquery is slightly different: first, it needs a GROUP BY clause just to be valid SQL; and also, there is no need for a conditional count.

PHP/MySql Select Comments Grouped with Replies

Ok, I think I got what you want now. I don't have a way to test the MYSQL version of this function quickly, but the SQLite version works great and, according to the documentation, the MySQL should work just as great. Might be a better way to do it on MySQL though.

SQLite:

SELECT a.*, IFNULL( b.Is_Reply_To, a.Comment_ID ) as Is_Reply_To FROM Comments a LEFT JOIN Comments_Reply b HAVING(Comment_ID) ORDER BY Is_Reply_To ASC, a.Comment_ID ASC

MySQL

SELECT a.*, IF( IS NULL(b.Is_Reply_To), a.Comment_ID, b.Is_Reply_To ) as Is_Reply_To FROM Comments a LEFT JOIN Comments_Reply b HAVING(Comment_ID) ORDER BY Is_Reply_To ASC, a.Comment_ID ASC

This produces these results for my on SQLite.

Comment_ID Is_Reply_to
1          1
10         1
11         1
2          2
12         2
3          3
13         3
14         3
4          4
5          5
6          6
7          7
8          8
9          9
15         15

---

Related Topics