Ordering comments By Date and Grouping By Replies To

Databases
1

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

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


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 :frowning: ). 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

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)

3

Thank you Rudy for your quick reply :slight_smile:

Reliable as ever :slight_smile:

4

If it is of any worth, i did buy your book :slight_smile: :rofl:

5

ta very much

every few pennies help, eh

:slight_smile:

6

i enjoyed reading your Categories and Subcategories article

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

7

yeah, but it’s broken at the moment

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

:slight_smile:

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

8

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