I have two tables, one is sections, this has an id, a parent_table and a parent_id, the other is pages which has a id and status (and other) columns. The parent of a section can be another section or a page, with according parent_table values. How could I find out using window functions whether a given section is on a status = 1 page or not? (using MySQL 8.0 (or MariaDB 10.2) but in a pinch I could convert into postgresql if badly necessary).
Example data:
id|parent_table|parent_id
1 |pages |1
2 |sections |1
3 |pages |2
4 |sections |2
id|status
1|1
2|0
To recreate the sample data:
create table pages (id int not null primary key, status int not null) collate utf8mb4_general_ci;
create table sections (id int not null primary key, parent_table varchar(20), parent_id int not null) collate utf8mb4_general_ci;
insert into pages values (1,1),(2,0);
insert into sections values (1,'pages',1),(2,'sections',1),(3,'pages',2),(4,'sections',2);
To get the corresponding page status for all sections, you can use the following recursive query:
with recursive r as (
select s.id as section_id, s.parent_id, s.parent_table
from sections s
union all
select r.section_id, s.parent_id, s.parent_table
from r
join sections s on s.id = r.parent_id and r.parent_table = 'sections'
)
select r.section_id, p.id as page_id, p.status
from r
join pages p on p.id = r.parent_id
where r.parent_table = 'pages';
Result:
| section_id | page_id | status |
| ---------- | ------- | ------ |
| 1 | 1 | 1 |
| 2 | 1 | 1 |
| 4 | 1 | 1 |
| 3 | 2 | 0 |
The recursion traverses the tree from any section to its root section. This way you can limit the result to a single section in the inner query. Eg. if you only want to know the status of section 3, you would use a WHERE clause in the first UNION part of the RCTE:
with recursive r as (
select s.id as section_id, s.parent_id, s.parent_table
from sections s
where s.id = 3 -- limit to a single section
union all
select r.section_id, s.parent_id, s.parent_table
from r
join sections s on s.id = r.parent_id and r.parent_table = 'sections'
)
select r.section_id, p.id as page_id, p.status
from r
join pages p on p.id = r.parent_id
where r.parent_table = 'pages';
Result:
| section_id | page_id | status |
| ---------- | ------- | ------ |
| 3 | 2 | 0 |