#StackBounty: #php #mysql #sql #ajax #recursive-query Order hierarchical query for comments with parent-child relation?

Bounty: 50

I have a comments system that uses the adjacency list to store hierarchical data (i believe), for example: MySql table has the columns id, parent_id, 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>

Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.