Возможно ли рекурсивно подсчитать количество проектов в категории одним запросом?

Question

[alestro]

Есть две таблицы projects и categories. Каждый проект имеет category_id.
Каждая категория имеет parent_id указывающий на саму себя.
Есть необходимость подсчитать количество проектов в каждой категории включая потомков.
В настоящий момент пытаюсь сделать это так, но такой запрос вызывает ошибку unknown column 'c2.id' in 'where clause'

SELECT c2.id, (SELECT COUNT(id) FROM projects WHERE category_id IN ((with recursive cte (id) AS 
(select id FROM categories where parent_id IN (c2.id) OR id IN (c2.id) union all select c.id from categories as c inner join cte on c.parent_id = cte.id) 
select distinct id from cte))) AS p_count FROM categories AS c2

Comments

[Akina]

Возможно ли рекурсивно подсчитать количество проектов в категории одним запросом?

Да. Ответ на очевидный следующий вопрос "Как?" зависит от точной версии MySQL.

Есть две таблицы projects и categories. Каждый проект имеет category_id.
Каждая категория имеет parent_id указывающий на саму себя.
Есть необходимость подсчитать количество проектов в каждой категории включая потомков.

Выложите это всё в виде CREATE TABLE + INSERT INTO (5-7 записей достаточно) и требуемого ответа для показанных данных.

Answer 1

[Rsa97]

Для MySQL 8

WITH RECURSIVE `cte` AS (
  SELECT `id` AS `root_id`, `id`
    FROM `categories`
  UNION SELECT `r`.`root_id`, `c`.`id`
    FROM `cte` AS `r`
    JOIN `categories` AS `c` ON `c`.`parent_id` = `r`.`id`
)
SELECT `c`.`root_id`, IFNULL(SUM(`p`.`count`), 0)
  FROM `cte` AS `c`
  LEFT JOIN (
    SELECT `category_id`, COUNT(*) AS `count`
      FROM `projects`
      GROUP BY `category_id`
  ) AS `p` ON `p`.`category_id` = `c`.`id`
  GROUP BY `c`.`root_id`