Implementing Recursive Comments in PHP/MySQL

I'm trying to write a commenting system, where people can comment on other comments, and these are displayed as recursive threads on the page. (Reddit's Commenting system is an example of what I'm trying to achieve), however I am confused on how to implement such a system that would not be very slow and computationally expensive.

I imagine that each comment would be stored in a comment table, and contain a parent_id, which would be a foreign key to another comment. My problem co开发者_JAVA百科mes with how to get all of this data without a ton of queries, and then how to efficiently organize the comments into the order belong in. Does anyone have any ideas on how to best implement this?

Try using a nested set model. It is described in Managing Hierarchical Data in MySQL.

The big benefit is that you don't have to use recursion to retrieve child nodes, and the queries are pretty straightforward. The downside is that inserting and deleting takes a little more work.

It also scales really well. I know of one extremely huge system which stores discussion hierarchies using this method.

Here's another site providing information on that method + some source code.

It's just a suggestion, but since I'm facing the same problem right now, How about add a sequence field (int), and a depth field in the comments table, and update it as new comments are inserted.

The sequence field would serve the purpose of ordering the comments. And the depth field would indicates the recursion level of the comment.

Then the hard part would be do the right updates as users insert new comments.

I don't know yet how hard this is to implement, but I'm pretty sure once implemented, we will have a performance gain over nested model based solutions.

I created a small tutorial explaining the basic concepts behind the recursive approach. As people have said above, the recursive function doesn't scale as well, however, inserts are far more efficient.

Here are the links:

http://www.evanpetersen.com/index.php/item/php-and-mysql-recursion.html

and

http://www.evanpetersen.com/index.php/item/php-mysql-revisited.html

I normaly work with a parent - child system.

For example, consider the following:

Table comment( commentID, pageID, userID, comment [, parentID] )

parentID is a foreign key to commentID (from the same table) which is optional (can be NULL).

For selecting comments use this for a 'root' comment:

SELECT * FROM comments WHERE pageID=:pageid AND parentID IS NULL

And this for a child:

SELECT * FROM comments WHERE pageID=:pageid AND parentID=:parentid

I had to implement recursive comments too. I broke my head with nested model, let me explain why :

Let's say you want comments for an article. Let's call root comments the comments directly attached to this article. Let's calls reply comments the comments that are an answer to another comment.

I noticed ( unfortunately ) that I wanted the root comments to be ordered by date desc, BUT I wanted the reply comments to be ordered date asc !! Paradoxal !!

So the nested model didn't help me to alleviate the number of queries.

Here is my solution :

Create a comment table with following fields :

id
article_id
parent_id (nullable)
date_creation
email
whateverYouLike
sequence
depth

The 3 key fields of this implementation are parent_id, sequence and depth. parent_id and depth helps to insert new nodes.

Sequence is the real key field, it's kind of nested model emulation.

Each time you insert a new root comment, it is multiple of x. I choose x=1000, which basically means that I can have 1000 maximum nested comments (That' s the only drawback I found for this system, but this limit can easily be modified, it's enough for my needs now).

The most recent root comment has to be the one with the greatest sequence number.

Now reply comments : we have two cases : reply for a root comment, or reply for a reply comment.

In both cases the algoritm is the same : take the parent's sequence, and retrieve one to get your sequence number. Then you have to update the sequences numbers which are below the parent's sequence and above the base sequence, which is the sequence of the root comment just below the root comment concerned.

I don't expect you to understand all this since I'm not a very good explainer, but I hope it may give you new ideas. ( At least it worked for me better than nested model would= less requests which is the real goal ).

I’m taking a simple approach.

• Save root id (if it’s comments then post_id)
• Save parent_id

Then fetch all comments with post_id and recursively order them on the client. I don’t care if there’s 1000 comments. This happens in memory.

It’s one database call, and that’s te expensive part.