I need to make sure we have support to create a hierarchy table based on a "father/son" table. Example:

F	S
a	b
b	c
b	d
b	e
e	f
e	g
b	m
z	n
m	k

Expected result:

L	L1	L2	L3
a	b	c
a	b	d
a	b	e
e	f
e	g
a	b	m	k
z	n

Any idea, suggestion?

Thank you

CodePudding user response：

with 
recursive cte(id, path) as
(
select f, f::text from   t where  f not in(select s from t where s is not null)
union all
select t.s, cte.path || '/' || t.s from cte join t on cte.id = t.f 
)

select  id      
       ,path[1] as root
       ,path[2] as l1
       ,path[3] as l2
       ,path[4] as l3
       ,path[5] as l4
from 
(
select  id 
       ,regexp_split_to_array(path, '/') as path
from    cte
) cte

id	root	l1	l2	l3	l4
a	a	null	null	null	null
z	z	null	null	null	null
b	a	b	null	null	null
n	z	n	null	null	null
m	a	b	m	null	null
e	a	b	e	null	null
d	a	b	d	null	null
c	a	b	c	null	null
k	a	b	m	k	null
g	a	b	e	g	null
f	a	b	e	f	null

Fiddle

CodePudding user response：

First three levels:

SELECT L0.F AS L, L1.F AS L1, L2.F AS L2, L3.S AS L3
FROM t L0
LEFT JOIN t L1
ON L0.S = L1.F
LEFT JOIN t L2
ON L1.S = L2.F
LEFT JOIN t L3
ON L2.S = L3.S
LEFT JOIN t nonexistent
ON L0.F = nonexistent.S
WHERE nonexistent.S IS NULL

Explanation:

• dimension1 is L0
• dimension2 is L1
• dimention3 is L2
• we ensure that L0 has no father by keeping only the L0 records that have no father

Maybe you need to change somewhat the logic, please let me know if I misunderstood the exact intention.

In more general terms, if you need n levels, then you will need to generate code. In PHP You can do it like this:

$SELECT = "SELECT L0.F AS L";
$FROM = "t L0";
for ($index = 1; $index < $n; $index  ) {
    $SELECT .= ", L{$index}.F AS L{$index}";
    $FROM .= " LEFT JOIN L" . $index . " ON L" . ($index - 1) . ".S = L" . $index . "F";
}
$FROM .= " LEFT JOIN t nonexistent ON L0.F = nonexistent.S ";
WHERE = "WHERE nonexistent.S IS NULL";
$query = "{$SELECT} ${FROM} {$WHERE}";

Of course, you can use the language you prefer to generate such an n-dimensional select, if PHP is not a technology you use, then kindly consider the above as a pseudo-code.

CodePudding user response：

Thanks for your answer.

I can only query the table with sql language and i don't know the max number of levels i have in the table.

Thanks