count number of replies to user comment

Question

I got multilevel comment system, I store comments in mysql database table with such fields:

id
article_id
user_id
date
content
comment_id

Where comment_id is parent comment's id.

how can i count number of replies to user comments after some specific date for all articles? e.g:

- comment1
-- comment1.1
--- comment1.1.1
-- comment1.2
-- comment1.3
--- comment1.3.1

if user posted comment1, i need query to return 5. If user posted comment 1.3 - return 1.

Answer 1

See Managing Hierarchical Data in MySQL for some ideas. One simple approach is to store the path in the comment tree like you listed above and do a LIKE query. E.g.:

SELECT COUNT(*) WHERE comment_path LIKE 'comment1.%'

You'll of course want an index on the comment_path column, which will be used as long as a % is only used on the end.