How can I sort from the least dependent view to the most dependent view?

Question

I have this data:

CREATE TABLE `ViewDependencies` (
  `ViewName` varchar(255) DEFAULT NULL,
  `DependentOn` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `ViewDependencies` (`ViewName`, `DependentOn`) VALUES
('Contacts.NaturalPersonViews', 'Contacts.NaturalPersons'),
('Contacts.NaturalPersonViews', 'Contacts.ContactInheritanceViews'),
('Contacts.NaturalPersonViews', 'Contacts.Persons'),
('Contacts.PersonViews',    'Contacts.PersonBaseViews'),
('Contacts.PersonViews',    'Contacts.ContactInheritanceViews'),
('Contacts.NaturalPersonCompositionViews',  'Contacts.NaturalPersons'),
('Contacts.NaturalPersonCompositionViews',  'Contacts.PersonCompositionViews'),
('Contacts.PersonBaseViews',    'Contacts.Persons'),
('Contacts.PersonBaseViews',    'Contacts.NaturalPersons'),
('Contacts.PersonBaseViews',    'Contacts.JuridicalPersons'),
('Contacts.NaturalPersonInheritanceViews',  'Contacts.NaturalPersons'),
('Contacts.NaturalPersonInheritanceViews',  'Contacts.PersonInheritanceViews'),
('Contacts.PersonCompositionViews', 'Contacts.PersonBaseViews'),
('Contacts.PersonCompositionViews', 'Contacts.ContactCompositionViews'),
('Contacts.RelationViews',  'Contacts.Relations'),
('Contacts.RelationViews',  'Contacts.JobTitles'),
('Contacts.RelationViews',  'Contacts.JuridicalPersonCompositionViews'),
('Contacts.RelationViews',  'Contacts.NaturalPersonCompositionViews'),
('Contacts.BankAccountViews',   'Contacts.BankAccounts'),
('Contacts.BankAccountViews',   'Contacts.PersonCompositionViews'),
('Contacts.AddressCompositionViews',    'Contacts.Addresses'),
('Contacts.AddressCompositionViews',    'Geo.LocationCompositionViews'),
('Contacts.JuridicalPersonInheritanceViews',    'Contacts.JuridicalPersons'),
('Contacts.JuridicalPersonInheritanceViews',    'Contacts.PersonInheritanceViews'),
('Contacts.ContactInheritanceViews',    'Contacts.Contacts'),
('Contacts.JuridicalPersonCompositionViews',    'Contacts.JuridicalPersons'),
('Contacts.JuridicalPersonCompositionViews',    'Contacts.ContactInheritanceViews'),
('Contacts.JuridicalPersonCompositionViews',    'Contacts.Persons'),
('Contacts.JuridicalPersonViews',   'Contacts.JuridicalPersons'),
('Contacts.JuridicalPersonViews',   'Contacts.ContactInheritanceViews'),
('Contacts.JuridicalPersonViews',   'Contacts.Persons'),
('Contacts.SocialProfileViews', 'Contacts.SocialProfiles'),
('Contacts.SocialProfileViews', 'Contacts.SocialNetworks'),
('Contacts.PersonInheritanceViews', 'Contacts.PersonBaseViews'),
('Contacts.PersonInheritanceViews', 'Contacts.ContactInheritanceViews'),
('Contacts.ContactCompositionViews',    'Contacts.Contacts'),
('Contacts.AddressViews',   'Contacts.Addresses'),
('Contacts.AddressViews',   'Geo.LocationCompositionViews'),
('Contacts.PhoneViews', 'Contacts.Phones'),
('Contacts.PhoneViews', 'Contacts.Contacts'),
('Contacts.PhoneViews', 'Geo.Countries');

This shows the dependencies between views and tables.

Some views like Contacts.PersonBaseViews are not dependent upon any other views. Some are just simple tables and they are also not dependent upon other views of course.

Some views, however, are dependent on other views.

I'm creating a tool to generate a database. For that, I need to create the least dependent views first. In other words, I need to create views based on their dependency graph.

Yet I'm stuck at this point.

How can I get a sorted result from the least dependent view, to the most dependent view (which means I need to exclude tables from the result, and I need to include views that are only inside the DependentOn column).

This is the db-fiddle

Answer 1

WITH RECURSIVE
cte AS (
  SELECT DependentOn ViewName, 1 level FROM ViewDependencies
  UNION
  SELECT ViewDependencies.ViewName, cte.level + 1
  FROM ViewDependencies
  JOIN cte ON cte.ViewName = ViewDependencies.DependentOn
  )
SELECT ViewName, MAX(level) level
FROM cte
GROUP BY 1
ORDER BY 2;

ViewName	level
Contacts.Relations	1
Contacts.SocialProfiles	1
Contacts.Contacts	1
Geo.LocationCompositionViews	1
Contacts.Addresses	1
Contacts.BankAccounts	1
Contacts.SocialNetworks	1
Contacts.Phones	1
Contacts.JobTitles	1
Geo.Countries	1
Contacts.JuridicalPersons	1
Contacts.Persons	1
Contacts.NaturalPersons	1
Contacts.PhoneViews	2
Contacts.AddressCompositionViews	2
Contacts.SocialProfileViews	2
Contacts.AddressViews	2
Contacts.ContactCompositionViews	2
Contacts.PersonBaseViews	2
Contacts.ContactInheritanceViews	2
Contacts.JuridicalPersonCompositionViews	3
Contacts.NaturalPersonViews	3
Contacts.PersonViews	3
Contacts.PersonInheritanceViews	3
Contacts.JuridicalPersonViews	3
Contacts.PersonCompositionViews	3
Contacts.NaturalPersonCompositionViews	4
Contacts.NaturalPersonInheritanceViews	4
Contacts.BankAccountViews	4
Contacts.JuridicalPersonInheritanceViews	4
Contacts.RelationViews	5

fiddle

Recommendation: after all views with level=1 processed requery your data excluding the rows which uses the views which are already processed.