I am trying to accumulate up, at every node in the hierarchy the number of direct descendants. For the nodes that do not have descendants the count should be 0.
In general I want to apply different types of counting/aggregation in multiple contexts where the hierarchy is not defined exactly such as this. Therefore I am interested in a recursive solution.
Consider the code that follows. How do I "invert" this query such that instead of calculating the depth I count the descendants and propagate up the numbers?
create table hierarchy (
name varchar(100),
location varchar(100),
parent_name varchar(100),
parent_location varchar(100)
) engine=InnoDB default charset=UTF8MB4;
truncate hierarchy;
insert into hierarchy values
('music', '/', NULL, NULL),
('classical', '/music', 'music', '/'),
('pop', '/music', 'music', '/'),
('rock', '/music', 'music', '/'),
('bach', '/music/classical', 'classical', '/music');
select * from hierarchy;
with recursive cte as
(
select name, location, parent_name, parent_location, 1 as depth
from hierarchy where parent_name is NULL and parent_location is NULL
union all
select a.name, a.location, a.parent_name, a.parent_location, depth + 1
from hierarchy as a inner join cte on a.parent_name = cte.name and a.parent_location = cte.location
)
select *
from cte;
Output is
name location parent_name parent_location depth
'music' '/' NULL NULL 1
'classical' '/music' 'music' '/' 2
'pop' '/music' 'music' '/' 2
'rock' '/music' 'music' '/' 2
'bach' '/music/classical' 'classical' '/music' 3
What I am ultimately interested in is this output:
name location parent_name parent_location descendents
'music' '/' NULL NULL 3
'classical' '/music' 'music' '/' 1
'pop' '/music' 'music' '/' 0
'rock' '/music' 'music' '/' 0
'bach' '/music/classical' 'classical' '/music' 0
You seem to be looking to count the number of direct descendants of each node. If so, I don't think that you need a recursive query: a simple subquery should do it:
select
h.*,
(select count(*) from hierarchy h1 where h1.parent_name = h.name) descendants
from hierarchy h
| name | location | parent_name | parent_location | descendants |
| --------- | ---------------- | ----------- | --------------- | ----------- |
| music | / | | | 3 |
| classical | /music | music | / | 1 |
| pop | /music | music | / | 0 |
| rock | /music | music | / | 0 |
| bach | /music/classical | classical | /music | 0 |