MySQL With Recursive

Question

I need to return my categories with the item quantity in each department, the parent categories must inherit from the child categories the quantity of products in each category.

Categories

+-------------+----------------------+--------+
| id | name                 |      parent_id |
+-------------+----------------------+--------+
|  1 | ELECTRONICS          |           NULL |
|  2 | TELEVISIONS          |              1 |
|  3 | TUBE                 |              2 |
|  4 | LCD                  |              2 |
|  5 | PLASMA               |              2 |
|  6 | PORTABLE ELECTRONICS |              1 |
|  7 | MP3 PLAYERS          |              6 |
|  8 | FLASH                |              7 |
|  9 | CD PLAYERS           |              6 |
| 10 | 2 WAY RADIOS         |              6 |
+-------------+----------------------+--------+

Product

+-------------+----------------------+--------+
| id | product                 | category_id |
+-------------+----------------------+--------+
|  1 | TV LCD 32"              |           4 |
|  2 | TV LCD 45"              |           4 |
|  3 | TV TUBE 29"             |           3 |
|  3 | IPOD                    |           7 |
+-------------+----------------------+--------+

Expected result

+-------------+----------------------+------------+
| id | name                 |    level| quantity |
+-------------+----------------------+------------+
|  1 | ELECTRONICS          |       1 |        4 |
|  2 | TELEVISIONS          |       2 |        3 |
|  3 | TUBE                 |       3 |        1 |
|  4 | LCD                  |       3 |        2 |
|  5 | PORTABLE ELECTRONICS |       2 |        1 |
|  6 | MP3 PLAYERS          |       3 |        1 |
+-------------+----------------------+------------+

I need to do using with recursive, because the speed is much higher than using nested

WITH RECURSIVE category_path (id, name, level, parent_id) AS
(
SELECT id, name, 1 level, parent_id
 FROM categories
  WHERE parent_id IS NULL
 UNION ALL
 SELECT c.id, c.name, level + 1, c.parent_id
  FROM category_path AS cp 
   JOIN categories AS c
    ON cp.id = c.parent_id
)
SELECT * FROM category_path

Time: 0.020s

using nested

SELECT
     parent.id,     
   parent.name,
     parent.parent_id,
   COUNT(departaments.product_id) 
FROM
   categories AS node 
   INNER JOIN
      categories AS parent 
      ON node.lft BETWEEN parent.lft AND parent.rgt 
   INNER JOIN
      departaments 
      ON node.id = departaments.categorie_id 
GROUP BY
   parent.id 
ORDER BY
   node.lft;

Time: 1.510s

Paul Spiegel Paul Spiegel · Accepted Answer · 2019-09-02T18:54:34

First write a query to get product count per category. This is quite simple:

with products_per_category as (
  select c.id, count(p.id) as pcount
  from categories c
  left join products p on p.category_id = c.id
  group by c.id
)
  select *
  from products_per_category
  order by id

db-fiddle

Then write a recursive CTE to generate a transitive closure:

with recursive rcte as (
  select c.id, c.id as ancestor_id
  from categories c
  union all
  select r.id, c.parent_id
  from rcte r
  join categories c on c.id = r.ancestor_id
)
  select *
  from rcte
  order by id, ancestor_id

The result will be like:

| id  | ancestor_id |
| --- | ----------- |
| 1   | 1           |
| 2   | 1           |
| 2   | 2           |
...
| 9   | 1           |
| 9   | 6           |
| 9   | 9           |
| 10  | 1           |
| 10  | 6           |
| 10  | 10          |

db-fiddle

It's like you get paths from root node to each category. Eg. for 9 the path is 1->6->9

If you order it by ancestor_id first, you will get:

| id  | ancestor_id |
| --- | ----------- |
| 1   | 1           |
...
| 10  | 1           |
| 2   | 2           |
| 3   | 2           |
| 4   | 2           |
| 5   | 2           |
| 3   | 3           |
...

db-fiddle

Here you can see, that category 2 (ancestor_id=2) has subcategories (id) 2,3,4,5. Note that every category has itself as subcategory. This will make the next step simpler.

Now all we need is to join the two CTEs and sum up the product counts:

with recursive products_per_category as (
  select c.id, count(p.id) as pcount
  from categories c
  left join products p on p.category_id = c.id
  group by c.id
), rcte as (
  select c.id, c.id as ancestor_id
  from categories c
  union all
  select r.id, c.parent_id
  from rcte r
  join categories c on c.id = r.ancestor_id
  where c.parent_id is not null
)
  select
    c.id,
    c.name,
    sum(p.pcount) as quantity
  from rcte r
  join categories c on c.id = r.ancestor_id
  left join products_per_category p on p.id = r.id
  group by c.id

Result:

| id  | name                 | quantity |
| --- | -------------------- | -------- |
| 1   | ELECTRONICS          | 4        |
| 2   | TELEVISIONS          | 3        |
| 3   | TUBE                 | 1        |
| 4   | LCD                  | 2        |
| 5   | PLASMA               | 0        |
| 6   | PORTABLE ELECTRONICS | 1        |
| 7   | MP3 PLAYERS          | 1        |
| 8   | FLASH                | 0        |
| 9   | CD PLAYERS           | 0        |
| 10  | 2 WAY RADIOS         | 0        |

db-fiddle

If you want to remove empty categories (quantity = 0), then just replace all LEFT JOINs with INNER JOINs.

Update

To get the level, you can use a subquery in the outer SELECT:

(select count(*) from rcte r2 where r2.id = c.id) as level

db-fiddle

MySQL With Recursive

1 Answers

Update