3

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.

  • 1
    Wouldn't comment1's query return 3? 1.1, 1.2, and 1.3 are direct children of comment1. – Zack Marrapese Mar 22 '11 at 17:43
  • This question may be useful reading, even though it is for Postgresql: [Is it possible to make a recursive SQL query?](http://stackoverflow.com/questions/53108/is-it-possible-to-make-a-recursive-sql-query) – Orbling Mar 22 '11 at 17:44
  • @Zack , my bad, for comment1 the return should be 5. –  Mar 22 '11 at 17:46

2 Answers2

3

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.

Paŭlo Ebermann
  • 73,284
  • 20
  • 146
  • 210
WhiteFang34
  • 70,765
  • 18
  • 106
  • 111
  • "LIKE" approach seems to be fast to implement, so i'll do it first. Thanks for the article link, i'll definitely look into it. –  Mar 22 '11 at 18:05
0

if it is possible, you can change your data schema to Nested Sets. With this schema you can count the answers in every hierarchy with a simple addition/substraction. Unfortunately I know only good tutorials in German :-/ for example this.

strauberry
  • 4,189
  • 5
  • 34
  • 50
  • The Nested Set Model is covered in [Managing Hierarchical Data in MySQL](http://dev.mysql.com/tech-resources/articles/hierarchical-data.html). – WhiteFang34 Mar 22 '11 at 17:58
  • @WhiteFang34: The article is now gone there, but available on its author's site: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ – Paŭlo Ebermann Oct 23 '11 at 14:24