How to build Threaded comments with a 1 or 2 queries?

Question

Can anyone suggest a creative database structure + fetching algorithm for a threaded comments system, that would output x amount of threads per page (with unlimited replies for each)?

I can run a query to get the threads, and in each instance of a loop, run another query to echo out the replies.... but that's a bad idea.

Answer 1

If you need only 2 levels, here's a way with one query:

Your table - id, parent_id, comment columns

Code

$rows = mysql_query('
  select *
  FROM
    comments
  ORDER BY
    id DESC');

$threads = array();
foreach($rows as $row) {
  if($row['parent_id'] === '0') {
    $threads[$row['id']] = array(
      'comment' => $row['comment'],
      'replies' => array()
    );
  } else {
    $threads[$row['parent_id']]['replies'][] = $row['comment'];
  }
}

In $threads you will have all your main threads and $threads[$id]['replies'] holds all replies. The threads are sorted - latest = first, add some paging and you're good to go.

Answer 2

Add two columns to the comment table: parentCommentId and rootCommentId.

parentCommentId is the id of the parent comment, and rootCommentId is the id of the comment that started this thread.

To display N threads, you'll need two queries:

1. Get N rows from the comment table where rootCommentId = id
2. Get all comments for these N threads

(You can combine these two into a single GroupBy query.)

Answer 3

This is similar to something I am using now. The only tricky part is calculating the next reply path to insert when somebody replies to a comment.

The Example Data

ID | Comment                      | Path
---+------------------------------+----------
0  | Comment #1                   | 01
1  | Comment #1 reply             | 01_01
2  | Comment #1 reply reply       | 01_01_01
3  | Comment #1 reply reply       | 01_01_02
4  | Comment #2                   | 02
5  | Comment #3                   | 03
6  | Comment #3 reply             | 03_01

The Example SQL

SELECT * FROM comments ORDER BY path

The Example PHP

while ($result = mysql_fetch_assoc($query)) {
    $nesting_depth = count(explode("_", $result['path']));
    $branch = str_repeat("--", $nesting_depth);
    echo $branch {$result['comment']}";
}

The Example Result

Comment #1
-- Comment #1 reply
---- Comment #1 reply reply
---- Comment #1 reply reply
Comment #2
Comment #3
-- Comment #3 reply

To make a reply to 01_01

SELECT path FROM comments WHERE path LIKE '01\_01\___'

$last_path = $row[0];
$last_path_suffix = substr($last_path,strrpos($last_path,'_')+1);
$next_path_suffix = str_pad($last_path_suffix+1,2,'0',STR_PAD_LEFT);
$next_path = substr($last_path,0,strlen($last_path)-strlen($last_path_suffix)).$next_path_suffix;

Answer 4

Having just encountered this problem and had to solve it I will add an answer here. The generic problem is that comments with replies is a tree sorting problem and relational databases are not well-suited to this. However, a comments database does have one very useful feature - they are arranged in sequence with replies always coming after the comments they are an answer to. This allows a rather simple programmatic solution; first select the comments and save them into an array sorted by id, then work through the array adding fields "thread" and "threadbase" where threadbase is the id of the original comment (e.g. 0045) and thread is the path of replies (e.g. 0045/0050/0120). php for this given an array of comments with id and reply_to fields is:

uasort($comments, fnMakeComparer(['id', SORT_ASC]));

$keys=array_keys($comments);

//go through the comments adding thread and threadbase
$n=count($comments);
for($x=0;$x<$n;$x++){
$key=$keys[$x];
$replyto=$comments[$key]['reply_to'];
$comments[$key]['thread']=$comments[$replyto]['thread']."/".$comments[$key]['id'];
$comments[$key]['threadbase']=substr($comments[$key]['thread'],0,6);
}

//resort comments by threadbase (most recent first) then thread (oldest first)
uasort($comments, fnMakeComparer((['threadbase', SORT_DESC]),['thread', SORT_ASC]),);