I ask to help with the formation of a query to the table. I don’t understand SQL queries at all, but the task was to display the number of responses to comments. There are 2 columns in the table:

  • id (comment id)
  • reply_to (with a value of 0 - if it is the head comment or given the id of the comment to which it is written, if it is the answer). There are no answers of the 3rd order, i.e. There is no answer to the answer.

The question is how to form a request to print the sum of responses to the head comment, i.e. collect values ​​for reply_to for each id of the head comment equal to it, and summarize them?

For a tip or answers 500 advantages in karma, regardless of religion and other anti-scientific nonsense.

  • nesting is endless? - Invision
  • The number of answers is not regulated, t.ch. yes - Nikolay
  • as nesting is infinite, if there are no 3rd order Answers, i.e. There is no answer to the answer. ? - splash58
  • do if you can test data here - sqlfiddle.com - splash58

3 answers 3

Solution based on

There are no answers of the 3rd order, i.e. There is no answer to the answer.

Circuit test

CREATE TABLE `items_comments` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `reply_to` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Request

 select c.id, (select count(*) from items_comments where reply_to= c.id ) as cnt from items_comments c where c.reply_to = 0

Do not display entries with no answers (where cnt = 0 )

 select c.id, (select count(*) from items_comments where reply_to= c.id ) as cnt from items_comments c where c.reply_to = 0 having cnt > 0
  • and the table here comments? - Nikolay
  • Rename to fit your requirements - Invision
  • well my whole table is called items_comments - Nikolay
  • renamed the table in the answer. - Invision
  • the request will display a list of items_comments of the first level and the number of responses. - Invision
 select t1.id, count(1) cnt from items_comments t1 join items_comments t2 on (t2.reply_to = t1.id) where t1.reply_to = 0 group by id

    You can additionally check if the nesting is still there, and you need answers only to the head comment.

     SELECT reply_to AS id, COUNT(id) FROM comments WHERE reply_to != 0 AND reply_to IN (SELECT id FROM comments WHERE reply_to = 0) GROUP BY reply_to;
    • and the table here comments? - Nikolay
    • By the way IN subquery is not needed at all. reply_to! = 0 conditions are enough to find answers - Mike
    • Yes, table comments. If you don’t have any nesting, then the subquery is not needed - Daniel-664