User commenting system - placing child comments (replies) under the parent comment

Databases
1

Hi, many websites allow readers to comment on the articles or content of the site. Lots of these sites just return each comment to the screen in the order they were created which is easy enough to implement.

However some sites allow readers to comment on other readers comments. These ‘reply’ comments as I will call them will appear under the comment they are replying to and are indented a bit to make the structure more clear. For example the structure might appear like so:


ARTICLE CONTENT HERE
This article has 9 comments:
--------------------------------
-Comment_1
--Comment_1_Reply_1
--Comment_1_Reply_2
--Comment_1_Reply_3

-Comment_2
-Comment_3
-Comment_4
--Comment_4_Reply_1
--Comment_4_Reply_2
------------------------------------
Leave a comment (form displayed)

For a real example see here (scroll down to the user comments). You will see a parent comment followed by indented replies to the parent comment if there are any. I was wondering how to achive this effect as I’m not sure how the SQL would work.

On my comment table I have the fields comment_id (Primary Key) and a replyTo_ID. When a user replies to say comment one which has a PK of 1, that number is inserted into the replyTo_ID field. I’ve tried sorting the results by comment_id and then replyTo_ID but it does not work.

Reluctantly I think I can do it with two queries, the first query gets the comment_id and in in the loop another query is made that fetches any replyTo_ID that matches the comment_id returned in the first query. I know this can work as I’ve done something similar before:


 FORM QUERY : SELECT parent_comments FROM comment_table

while parent_comments are found 
{
 define variable : parent_id
 define variable : parent_comment
 echo parent_comment

   FORM QUERY : SELECT child_comments FROM comment_table WHERE  reply_to_id=parent_id
  IF rows found 
  {
     while child comments are found
     {
        define variable : child_id
        define variable : child_comment
        echo child_comment
     }
  }
}

Obviously this is making multiple calls (and pointless ones if there are no reply comments to retrieve) to the database which is something I would like to avoid. Therefore could it be done with just one query?

2

yup… but you’ll need a separate join for each “level” that you want to retrieve

this gets the comments on an article –


SELECT ...
  FROM comments AS c1
 WHERE c1.article_id = 42

this gets the comments plus replies to those comments –


SELECT ...
  FROM comments AS c1
LEFT OUTER
  JOIN comments AS c2
    ON c2.replyTo_ID = c1.comment_id
 WHERE c1.article_id = 42

this gets the comments plus replies to those comments plus replies to the replies –


SELECT ...
  FROM comments AS c1
LEFT OUTER
  JOIN comments AS c2
    ON c2.replyTo_ID = c1.comment_id
LEFT OUTER
  JOIN comments AS c23
    ON c3.replyTo_ID = c2.comment_id
 WHERE c1.article_id = 42
3

Thanks for your help r937, pointed me in the right direction. Took a while to play around with the query to match exactly what I require but it looks like it’s working now so thank you :slight_smile: