Modified preorder traversal for comment system

Question

Hi, I'm trying to make a comments system for a blog. I have the modified preorder traversal system working (used this guide: http://dev.mysql.com/tech-resources/articles/hierarchical-data.html).

I have a few issues though. I do not think that guide explains how to manage having different blog posts, and adding a comment that is not a reply.

my comments table looks like:

+-------------+----------------------+-----+-----+

| comment_id  | message              | lft | rgt |
auto increment
+-------------+----------------------+-----+-----+

Is this a good way to manage this:

I add column to my comments table called "blog_post_id" and "root". When I make a blog post I then add an entry into the comments table with the blog_post_id, and root set to true. Then, the lft is the comment_id and the right is the comment_id + 1.

To load the comments for a blog post I would find the lft and rgt WHERE the blog_post_id = x and root = true, then select all the comments between the lft and rgt where the blog_post_id is x...

I just came up with this method, so I'm pretty sure there must be a better way.

Thanks

Answer 1

How about adding a blog_post_id column and considering the blog post itself as a virtual comment (the root of the comment tree)? Then it can have multiple children.

Using this method, you could use all the algorithms from that article unchanged, with the caveat that you add AND blog_post_id == foo to all the queries and always ignore the root comment (since it stands for the blog itself.)

Answer 2

You "came up" with a very good method. It is the standard way to manage nested comments. There is quite a bit of literature on this and implementations as well.

Take a look here for an example implementation: http://api.rubyonrails.org/classes/ActiveRecord/Acts/NestedSet/ClassMethods.html