Is it possible to recursively count the number of projects in a category with a single query?

Question

MySQL

There are two tables projects and categories. Each project has a category_id.
Each category has a parent_id pointing to itself.
There is a need to count the number of projects in each category including descendants.
Currently trying to do it like this, but such a query throws an error 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

Answer 1

For 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`