How to represent hierarchical data in relational databases + recursively query them || PostgreSQL

Buy me a coffee if this is useful 😀
www.buymeacoffee.com/rowadz
The article from the video:
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;

[@guilhermealvessilveira8938]

Excellent explanation, thank you.

[@marceli-wac]

NIce one, thanks for sharing!

[@rafiktaamma9959]

thank you my brother . simple and usefull

[@AkysChannel]

For people struggling with never-returning query: try to make the condition in the "non-recursive term" part of the query to be `"your_column_name" IS NULL` and not any other condition.

[@CarpetEraser]

this is great, thx!

[@terrymoles9787]

I still don't fully understand how this works but I've gotten to my end goal and am massively grateful for your help getting there, thank you!

[@kssabin786]

Recursive CTE, we can use the 1st cte results as a input to next level, so upto some criteria we can use this method like tree structure - node, leaf..

[@lefteriskontomarkos9097]

Excellent explanation!!! I was revisiting a recursive CTE query of mine that was throwing an error, and your video greatly helped me understand in depth the concept

[@Rowadz]

Glad it helped!

[@syakhiskk]

❤

[@jtjt8777]

I am trying implement a threaded comment table and I learned a lot from your video. I saw you upload so many video. I applaud your dedication! Rowadz

[@Rowadz]

Glad it was helpful :)

[@cutiiieekudi6817]

0:41 DH

[@poojathakur5547]

Hi - i have return the cte but i want to used it with the crosstab function to transpose the values from rows to columns , but i am getting an error

[@rembautimes8808]

Thanks this is an excellent tutorial .Joined as sub

[@mkroven]

Great content. Thank you.. I used closure table for a ecommerce product categories to query ancestor category and descandants. Your tree solution seems better.

[@Rowadz]

Thanks for sharing, I didn't know about closure tables I'll read about them

[@tendimuchenje7984]

this is great content. I am trying to write my own recursive query, but I am running into issues. Is there anyway to reach your for a brief consultation?

[@carolinarojano7298]

Hello, what can i do if i'm working with something similar like, getting all the prerequisites for a course, which i did like this
WITH RECURSIVE db_req AS(
SELECT
course_id,
req_id

FROM pre_requisitos
WHERE course_id = 2

UNION

SELECT
p.course_id,
p.req_id
FROM pre_requisitos p
INNER JOIN db_req as d ON d.req_id = p.course_id
) SELECT req_id FROM db_req;
But now i need all the prerequisites of all courses, i'm guessing i have to iterate over all the courses but don't know how exactly

[@Rowadz]

not sure If I have a soultion for u, but can adding the course root id in each row help?
WITH RECURSIVE db_req AS
(
SELECT
course_id,
req_id,
course_id
FROM
pre_requisitos
UNION
SELECT
p.course_id,
p.req_id,
db_req.course_id,
FROM
pre_requisitos p
INNER JOIN
db_req as d
ON d.req_id = p.course_id
)
SELECT
req_id
FROM
db_req;

[@Rowadz]

maybe also getting the level of each node with the path of the privouse courses ids (requisitos) could help
like this
WITH RECURSIVE db_req AS(
SELECT
course_id,
req_id,
course_id
0 as emp_level,
e1.id::VARCHAR as c_id
FROM pre_requisitos
UNION
SELECT
p.course_id,
p.req_id,
db_req.course_id,
db_req.nesting_level+1,
db_req.c_id::VARCHAR || ',' || p.id::VARCHAR
FROM pre_requisitos p
INNER JOIN db_req as d ON d.req_id = p.course_id
) SELECT req_id FROM db_req;

[@santoshadhikari9933]

how do you get unique only element in single column of that whole hierarchy

[@Rowadz]

I'm not sure I understand completely but try doing that after the recersive CTE.
The result from the CTE is just like a table, you can do anything on it.

[@ftnsco]

Why not use LTREE?

[@Rowadz]

Because I've never heard of it XD
but it looks really interesting