** Is there any way that I can visualize the steps of recursive CET in mysql (Something probably similar to https://pythontutor.com/
I am trying to understand the order of execution for the recursive CET Part of my query, I am reaching the intended output anyway which is counting the total family members per each member having null parent
Schema (MySQL v8.0)
CREATE TABLE Family (
person INTEGER not null PRIMARY KEY,
name VARCHAR(255) NOT NULL,
parent INTEGER
)
;
INSERT INTO Family (person, name, parent) VALUES
(1,'Sabri',NULL),
(2, 'Reo',1),
(3, 'Fadaa',1),
(4, 'Sahar',1),
(5, 'Helen',3),
(6, 'Mimi', 3),
(7, 'Reo',1),
(8, 'Micheal',Null),
(9, 'Antoni',8),
(10, 'Selen',9);
Query #1
WITH RECURSIVE cte as
(
SELECT person, person as root_id, parent
FROM Family
UNION ALL
SELECT cte.person, Family.person, Family.parent as parent
FROM Family
JOIN cte ON cte.parent = Family.person
),
Agg_data as
(
SELECT * FROM cte
ORDER BY person
),
Top_members as
(
Select Person, root_id
FROM Agg_data where parent is null
)
Select distinct root_id as Top_parents,
count(Distinct if( root_id !=person, person, NULL)) AS members_Count
from Top_members
group by 1;
Output
Top_parents | members_Count |
---|---|
1 | 6 |
8 | 2 |
However I am Interested in understanding the order of recursion execution for the output below
Like iteration 1 output is ? iteration 2 output is ? iteration 3 output is ? and so on
select * from cte, this is the output
person | root_id | parent |
---|---|---|
1 | 1 | |
2 | 2 | 1 |
3 | 3 | 1 |
4 | 4 | 1 |
5 | 5 | 3 |
6 | 6 | 3 |
7 | 7 | 1 |
8 | 8 | |
9 | 9 | 8 |
10 | 10 | 9 |
2 | 1 | |
3 | 1 | |
4 | 1 | |
5 | 3 | 1 |
6 | 3 | 1 |
7 | 1 | |
9 | 8 | |
10 | 9 | 8 |
5 | 1 | |
6 | 1 | |
10 | 8 |