I have a comments system that uses the
adjacency list to store hierarchical data (i believe), for example: MySql table has the columns
date, … , where a comment without a
parent_id is a main comment and a comment with a
parent_id is naturally a reply.
On initial page load i make an Ajax call that loads all the comments without a
parent_id, so all the main comments.
SELECT * FROM comms WHERE parent_id IS NULL
Now if any of the comments have replies a button like “load replies” appears for that comment and on click another call is made that loads all the comments that have as
parent_id that comment
id, then the recursive query loads the replies of replies and so on. And that works pretty well the problem is that from the order they are loaded you can’t really tell what is a replay to what.
So what i want is to order them so that a replay is under the comment that it belongs.
Now is this possible only from sql doing something like
ORDER BY id = parent_id, ordering them so that they somewhat make sense or should i handle this from php? Or should i just start over and store them in a different way?
Edit: part of the second query (example taken from this answer i found a while back)
SELECT date_p, parent_id, id FROM (SELECT * FROM comms) rec, (SELECT @pv := 14) initialisation WHERE find_in_set(parent_id, @pv) > 0 AND @pv := concat(@pv, ',', id) ORDER BY ?
If i would use the “Alternative 1” provided in the answer i liked for, would the method for ordering be different or better?
This is what I am trying to achive:
<p>Main comm 1</p> <p>reply to main comm 1</p> <p>another reply to main comm 1</p> <p> replay to reply of main comm 1</p> <p> yet another reply to main comm 1</p> <p>Main comm 2</p> <p>Main comm 3</p>