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

Buy me a coffee if this is useful 😀

The article from the video:

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
0 as emp_level,
from employees e1

union all

select
emp_level+1,
from employees e2

) select * from employees_tree;

[terrymoles]

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!

[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.

[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.

[lefteriskontomarkos]

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

[rafiktaamma]

thank you my brother . simple and usefull

[poojathakur]

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

[rembautimes]

Thanks this is an excellent tutorial .Joined as sub

[tendimuchenje]

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?

[santoshadhikari]

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

[carolinarojano]

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