CODEWITHSUNDEEP
mysqlsqlhierarchyself-join
user346443
user346443

Reputation: 4862

MySQL self join with limited hierarchy

Hi I have a self joining MySQL table I am using for comments and replies.

CREATE TABLE comments (id INT, parent_id INT, comment VARCHAR(50));

INSERT INTO comments VALUES
 (1,     0,           'comment 1'            ),
 (2,     0,           'comment 2'            ),
 (3,     0,           'comment 3'            ),
 (4,     1,           'comment 1 - reply 1'  ),
 (5,     0,           'comment 4'            ),
 (6,     3,           'comment 3 - reply 1'  ),
 (7,     1,           'comment 1 - reply 2'  ),
 (8,     0,           'comment 5'            );

There is only ever one level of replies. That is, a reply can only ever be associated with a top level comment (where parent_id = 0).

I using the following query to show each top level comment (where parent_id = 0) and each of comments associated replies.

SELECT *
FROM comments 
ORDER BY IF(parent_id = 0, id, parent_id) desc , parent_id != 0, id desc

Output:

id  parent_id   comment
-------------------------
8      0        comment 5
5      0        comment 4
3      0        comment 3
6      3        comment 3 - reply 1
2      0        comment 2
1      0        comment 1
7      1        comment 1 - reply 2
4      1        comment 1 - reply 1

The current query is working well for what I need.

My question is how can I limit the number of replies for each comment? eg. Show the latest 50 top level comments with a maximum of 2 replies for each comment.

Here is a SqlFiddle if it helps

Upvotes: 0

Views: 677

Answers (2)

user346443
user346443

Reputation: 4862

First parameter of Limit (offset) controls the number of replies

SELECT *, 

(SELECT COUNT(id) FROM comments r where r.parent_id = c.id) AS number_of_replies

FROM comments c

WHERE IFNULL((SELECT e.id FROM comments e WHERE e.parent_id != 0 AND 
    e.parent_id = c.parent_id ORDER BY e.id DESC LIMIT 2, 1), 0) < c.id

ORDER BY IF(parent_id = 0, id, parent_id) desc , parent_id != 0, id desc

Upvotes: 0

mucio
mucio

Reputation: 7119

Try this:

EDIT:

SELECT pc.id,
       pc.parent_id,
       pc.comment
  FROM (
          SELECT id,
                 parent_id,
                 comment,
                 @parentRank := @parentRank + 1 AS rank
            FROM comments,
                 (SELECT @parentRank := 0) pcr
           WHERE parent_id = 0
        ORDER BY id DESC                      
       ) pc
 WHERE pc.rank <= 5
 UNION
SELECT cc.id,
       cc.parent_id,
       cc.comment
  FROM (
          SELECT id,
                 parent_id,
                 comment,
                 @childRank := if(@current_parent_id = parent_id, @childRank + 1, 1) AS rank,
                 @current_parent_id := parent_id
            FROM comments,
                 (SELECT @childRank := 0) cr
           WHERE parent_id in (
                                SELECT id
                                  FROM (
                                         SELECT id,
                                                @parentRank := @parentRank + 1 AS rank
                                           FROM comments,
                                                (SELECT @parentRank := 0) pcr
                                          WHERE parent_id = 0
                                       ORDER BY id DESC                      
                                       ) pc
                                 WHERE pc.rank <= 5
                              ) 
        ORDER BY parent_id DESC,
                 id DESC
             ) cc
       WHERE cc.rank <= 1
ORDER BY IF(parent_id = 0, id, parent_id) desc , parent_id != 0, id desc

I did a demo in SQLFiddler

Upvotes: 1

Related Questions