SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Apr 2005
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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:

    Code:
    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:

    Code:
     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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Dorza View Post
    Therefore could it be done with just one query?
    yup... but you'll need a separate join for each "level" that you want to retrieve

    this gets the comments on an article --
    Code:
    SELECT ...
      FROM comments AS c1
     WHERE c1.article_id = 42
    this gets the comments plus replies to those comments --
    Code:
    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 --
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Apr 2005
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •