How to represent hierarchical data in relational databases + recursively query them || PostgreSQL
Buy me a coffee if this is useful 😀
https://www.buymeacoffee.com/rowadz
The article from the video:
https://www.postgresqltutorial.com/po...
00:00 Introduction
00:33 Example of hierarchical data in a relational database
02:44 Small Intro about recursive CTEs in PostgreSQL
04:34 Recursive CTE example to get all the employees under a supervisor
08:53 Getting the depth (level) of each node [employee]
10:08 Getting a list of all the parent nodes [supervisors]
12:10 How I can use this in any ORM?
the final query::
WITH RECURSIVE employees_tree as (
select
e1.id,
e1.supervisor_id,
e1.username,
0 as emp_level,
e1.id::VARCHAR as emp_id
from employees e1
where e1.id = 2
union all
select
e2.id,
e2.supervisor_id,
e2.username,
emp_level+1,
emp_id::VARCHAR || ',' || e2.id::VARCHAR
from employees e2
join employees_tree et on et.id = e2.supervisor_id
) select * from employees_tree;
Watch video How to represent hierarchical data in relational databases + recursively query them || PostgreSQL online without registration, duration hours minute second in high quality. This video was added by user Rowadz 19 August 2021, don't forget to share it with your friends and acquaintances, it has been viewed on our site 20,029 once and liked it 301 people.