slothy — 2010-11-19T09:09:31-05:00 — #1
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
replyTo (null if top ancestor an existing id if a reply)
So for example the following structure may be a snapshot
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 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.
r937 — 2010-11-19T09:16:56-05:00 — #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)
slothy — 2010-11-19T09:30:23-05:00 — #3
Thank you Rudy for your quick reply
Reliable as ever
slothy — 2010-11-19T09:34:14-05:00 — #4
If it is of any worth, i did buy your book :rofl:
r937 — 2010-11-19T09:36:02-05:00 — #5
ta very much
every few pennies help, eh
slothy — 2010-11-19T09:42:14-05:00 — #6
i enjoyed reading your Categories and Subcategories article
Is there an index page for what is on sqllessons Rudy?
r937 — 2010-11-19T10:11:56-05:00 — #7
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
slothy — 2010-11-19T10:45:51-05:00 — #8
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