Implementing recursive comments in PHP / MySQL

Question

I am trying to write a comment system where people can comment on other comments and they are displayed as recursive streams on the page. (The Reddit Comment System is an example of what I'm trying to achieve), however, I am confused about how to implement a system that will not be very slow and computationally expensive.

I assume that each comment will be stored in the comments table and contain parent_id, which will be the foreign key for another comment. My problem is how to get all this data without a ton of requests, and then how to organize comments in this order efficiently. Does anyone have any ideas on how to best implement this?

Answer 1

Try using a nested dialing model. This is described in Managing Hierarchical Data in MySQL .

The big advantage is that you don't need to use recursion to retrieve the child nodes, and the queries are pretty simple. The disadvantage is that inserting and deleting requires a bit more work.

It also scales very well. I know one extremely huge system that stores discussion hierarchies using this method.

Answer 2

Here is another site containing information about this method + some source code.

Answer 3

I work fine with parent-child system.

For example, consider the following:

Table Comment (CommentID, PageId, User ID, Comment [, parentID])

parentID is the foreign key for commentID (from the same table), which is optional (may be NULL).

To select comments, use this for the "root" comment:

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

And this is for a child:

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

Answer 4

I created a short tutorial explaining the basic concepts of a recursive approach. As people said above, a recursive function also does not scale, however inserts are much 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

Answer 5

This is just a suggestion, but since I ran into the same problem right now, How about adding a sequence field (int) and depth field to the comments table, and update it when new comments are inserted.

The sequence field will be used to organize comments. And the depth field will indicate the comment recursion level.

Then the hard part will make the correct updates, as users insert new comments.

I don’t yet know how difficult it is to implement it, but I’m sure that once it was implemented, we will have an increase in productivity compared to the embedded solution model.

Answer 6

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

Let's say you need comments on the article. Let's call root comments comments directly related to this article. Allows you to respond to comment comments that are a response to another comment.

I noticed (unfortunately) that I wanted the comments of the root to be ordered by desc date, BUT I wanted the comments to be sent on a date from the date !! Paradoxically !!

Thus, the nested model did not help me reduce the number of queries.

Here is my solution:

Create a comment table with the following fields:

ID
article_id
parent_id (nullable)
date_creation
Email
whateverYouLike
sequence
Depth

3 key fields of this implementation: parent_id, sequence and depth. parent_id and depth help insert new nodes.

Consistency is a real key field, it is a kind of emulation of a nested model.

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

The most recent root comment should be with the highest sequence number.

Now answer the comments: we have two cases: the answer for the root comment or the response for the comment.

In both cases, the algorithm is the same: take the parent sequence and get it to get the sequence number. Then you need to update the sequence numbers that are below the parent sequence and above the base sequence, which is the sequence of root comments just below the corresponding root comment.

I do not expect you to understand all this, because I am not a very good explanator, but I hope this can give you new ideas. (At least this worked better for me than the nested model: = fewer queries, which is the real goal).