Order hierarchical query for comments with parent-child relation?

Question

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>

Answer 1

As this requires recursion, and MySQL pre v8 doesn't really support that, I would be inclined to solve the problem in PHP, which is good at recursion. The basic routine is quite simple:

function show_children($db, $parent) {
    $sql = "SELECT * FROM comms WHERE parent_id " . ($parent ? "= $parent" : "IS NULL") . " ORDER BY date_p ASC";
    $result = $db->query($sql);
    if (!$result) return;
    while ($row = $result->fetch_assoc()) {
        echo "<p>" . $row['cmt'] . "</p>";
        show_children($db, $row['id']);
    }
}

show_children($db, 0);

Starting with a call to show_children with a parent_id of 0 will fetch all the top-level comments (those with parent_id = NULL) and the recursion will show all the replies in order of date, but also in order of their parents (whether they are comments or replies).

You can also add a "level" parameter to allow styling the output:

function show_children($db, $parent, $level = 0) {
    $sql = "SELECT * FROM comms WHERE parent_id " . ($parent ? "= $parent" : "IS NULL") . " ORDER BY date_p ASC";
    $result = $db->query($sql);
    if (!$result) return;
    while ($row = $result->fetch_assoc()) {
        echo "<p class=\"level$level\">" . $row['cmt'] . "</p>";
        show_children($db, $row['id'], $level+1);
    }
}

show_children($db, 0);

Note I've assumed mysqli as the db interface.

Using this routine, with this input data:

id  date_p      parent_id   cmt
1   2018-03-21  (null)      Main comm1
2   2018-03-22  (null)      Main comm2
3   2018-03-22  1           reply to main comm 1
4   2018-03-23  1           another reply to main comm 1
5   2018-03-23  1           yet another reply to main comm 1
6   2018-03-24  4           replay to reply of main comm 1
7   2018-03-24  (null)      Main comm3

you will get this output:

<p class="level0">Main comm1</p>
<p class="level1">reply to main comm 1</p>
<p class="level1">another reply to main comm 1</p>
<p class="level2"> replay to reply of main comm 1</p>
<p class="level1"> yet another reply to main comm 1</p>
<p class="level0">Main comm2</p>
<p class="level0">Main comm3</p>

Answer 2

Great Question:

What you have now is a great idea:

I would store it in 3 different tables,

tb1) for the Main comment tb2) for the reply to main comment tb3) for the reply to reply to main comment.

tb1 contains columns like id (auto_increment) becomes your main comment id

tb2) contains columns like id(auto_increment), main_comment_id, reply_to_main_comment_id

tb3) has id(auto_increment), main_comment_id, reply_to_main_comment_id, reply_reply_id

<p1>main_comment_id</p1>
  <p2>reply_to_main_comment_id</p2>
     <p3>reply_reply_id</p3>

logically like the following:

    if(p1){
       post to tb1
    }elseif(p2){
       post to tb2
    }elseif(p3){
       post to tb3
    }


To get the comments for p1, (Select comments from tb1)
To get comments for p2, (Select comments from tb2 where main_comment_id=$id)
To get comments for p3, (Select comments from tb3 where main_comment_id=$id 
 and reply_to_main_comment_id=$id2)

$sql ("SELECT comments from tb3 WHERE main_comment_id=$id1 AND reply_to_main_comment_id=$id2 ORDER BY date DESC");

when you call your comments, of course, you will display them chronologically, latest to oldest, your p2 becomes main comment for p3.. it's like a parent and a child only, but you display it in an extended relation way....This will but the comments in the right place....

hope this works. I have not tested this but I have developed a database with similar principle....

code sample getting main comment:

$comment1 = $_POST['comment1'];
$comment1_sql = ("Insert INTO tb1 (main_comment) VALUES ($comment1)");
$comment_result = mysqli_query($conn, $comment_sql1);

code sample reply main comment:

$comment2 = $_POST['comment2'];
$comment2_sql = ("Insert INTO tb2 (reply_to_main_comment) VALUES ($comment2)");
$comment_result = mysqli_query($conn, $comment_sql2);

Same as comments reply to the reply of the main comments

Answer 3

I work on MSSQL and I am very sure that CROSS APPLY will help you . I am assuming that MYSQL does have that join .

eg

SELECT A.PARENT_ID,A.COMMENT,* FROM T A 
CROSS APPLY T B 
where a.id = 3