0

** 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

View on DB Fiddle

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

  • I don't think there's anything built-in for this. Maybe tools like MySQL Workbench can do it, but I doubt it. – Barmar Feb 15 '23 at 18:13
  • 1
    The iterations are basically the depth of the tree. See the answers to the duplicate question how to retrieve the depth at each iteration. You obviously need to work out how to visualise the output, but that's not going to be in mysql. – Shadow Feb 15 '23 at 18:21

0 Answers0