SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2004
    Location
    England
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Ordering comments By Date and Grouping By Replies To

    Hi Folks,

    I am developing a little web application and i want to have a rudimentary commenting system. This has turned into an academic exercise

    I want a user the ability to reply to a comment which itself may be a reply.

    The comments table would be something like

    id
    commentText
    commentDate
    replyTo (null if top ancestor an existing id if a reply)
    priority (optional)

    So for example the following structure may be a snapshot
    Code:
    Comment 1
      Comment 3 Reply to 1
        Comment 11 Reply to 3
           Comment 16 Reply to 11
      Comment 18 Reply to 1
        Comment 27 Reply to 18
           Comment 29 Reply to 27
      Comment 5 Reply to 1
      Comment  19 Reply to 1
        Comment 30 Reply to 19
    Comment 2
      Comment 6 Reply to 2
    Because of the recursiveness of the comments i cannot think of how i can get an ordered system which i then then just dump to a div with an indentation to imply the level.

    Can anybody help me pls in how to get an ordered recordset using Mysql (if it is possible even ). I am stumped

    There may be a restriction say of 3 levels say if that is any help ie there can be a reply to a reply, but not a reply to a reply to a reply

    They would be chronologically-ordered in reverse (by ancestral comment) and maybe by priority of ancestral comment.

    I ave implemented a similar system using a comment table and a replies table and just merged the two.

    I was basically wondering if it were possible to use a single table and have any nested level.

    Thank you

    Alex

  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)
    your replyTo column indicates that you are using the adjacency model

    see Categories and Subcategories for further information on returning chained results

    this is definitely feasible if you have a limit on the number of levels (you say 3 but you could make it up to 15 without worrying about performance)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2004
    Location
    England
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you Rudy for your quick reply

    Reliable as ever

  4. #4
    SitePoint Enthusiast
    Join Date
    Jul 2004
    Location
    England
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If it is of any worth, i did buy your book

  5. #5
    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)
    ta very much

    every few pennies help, eh

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Enthusiast
    Join Date
    Jul 2004
    Location
    England
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i enjoyed reading your Categories and Subcategories article

    Is there an index page for what is on sqllessons Rudy?

  7. #7
    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 slothy View Post
    Is there an index page for what is on sqllessons Rudy?
    yeah, but it's broken at the moment

    no estimated fix date either, sorry (shoemaker's children syndrome)



    here's the other published article (i have a dozen or so more on my "to do" list) -- Minimize Bandwith in One-to-Many Joins
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Enthusiast
    Join Date
    Jul 2004
    Location
    England
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    no problem

    thanks nonetheless

    lol like idea of shoemaker's children syndrome

    just realised if i add a level field (implicit when adding a comment as you can determine the level to which a user is replying to (and adding 1) then just adding another ORDER BY parameter

    now makes algorithm easier


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
  •