SQL How count all replies of a single comment on several levels

Question

I am working on comment system, I have to count all replies of a single comment on several levels.

Like this:

Parent
    ->child
        -> child

Parent
    -> child
    -> child
        ->child

My Sql is :

CREATE TABLE IF NOT EXISTS `comment` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT 'This is primary key of the table',  
  `parent_id` bigint(11) NOT NULL, 
  `content` text NOT NULL,
  PRIMARY KEY (`comment_id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=unicode_ci AUTO_INCREMENT=8 ;



INSERT INTO `comments` (`id`, parent_id`, `content`) VALUES
(1, 0, 'Parent'),
(2, 1, 'child'),
(3,  2, 'child'),
(4,  3, 'child'),
(5,  1, 'child2'),
(6, 0, 'Parent2'),
(7,  6,'child of parent2');

Possible duplicate of [How to create a MySQL hierarchical recursive query](https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) — Madhur Bhaiya, Oct 18 '18 at 12:27
Your design does not allow you to do this. Change to nested set model. See https://stackoverflow.com/questions/31641504/adjacency-list-model-vs-nested-set-model-for-mysql-hierarchical-data — Salman A, Oct 18 '18 at 12:29

score 0 · Answer 1 · answered Oct 18 '18 at 13:05

Try below query:

select count(*)
from comments c0
join comments c1 on c0.id = c1.parentid
-- in case if child comment doesn't have any children, we still need to keep it
left join comments c2 on c1.id = c2.parentid
where c0.id = 1 --particular id for which we want to count children

SQL How count all replies of a single comment on several levels

1 Answers1