I'm having issues on building a tree starting from a table.
I'm following the tutorial from D.Fontaine:
but something is wrong in my query. here is a subset of rows from this table: http://dpaste.com/1NE8X6G
- root is '57b2e67b-5862-499a-a471-0f2f6b23440e'
- under the root, there are two children '5c51558b-1180-495f-88c3-f7af49bafcf3' and '3962f997-9e14-4cac-a95f-dc20c077a531'
- each one of those has other children and so on.
the expected result would be:
[
{'Name': '57b2e67b-5862-499a-a471-0f2f6b23440e',
'Sub Classes': [
{'Name': '5c51558b-1180-495f-88c3-f7af49bafcf3',
'Subclasses': [
]},
{'Name': '3962f997-9e14-4cac-a95f-dc20c077a531',
'Subclasses': [
{'Name': '5c51558b-1180-495f-88c3-f7af49bafcf3',
'Subclasses': [
...
]}
]}
}
]
but it's not so I'm messing up something. here's the query:
with recursive rels_from_parents as
(
select sel.lo_id, '{}'::uuid[] as parents, sel.level as _level
from (select * from myproj_loparentrelation where root_id = '57b2e67b-5862-499a-a471-0f2f6b23440e') as sel
where sel.parent_id = sel.root_id
union all
select c.lo_id, parents || c.parent_id, c.level as _level
from rels_from_parents p
join myproj_loparentrelation c
on c.parent_id = p.lo_id
where not c.lo_id = any(parents)
),
rels_from_children as
(
select c.parent_id,
json_agg(jsonb_build_object('lo_id', c.lo_id::text))::jsonb as js
from rels_from_parents tree
join myproj_loparentrelation c using(lo_id)
where level > 0 and not lo_id = any(parents)
group by c.parent_id
union all
select c.parent_id,
jsonb_build_object('Name', c.lo_id::text)
|| jsonb_build_object('Sub Classes', js) as js
from rels_from_children tree
join myproj_loparentrelation c on c.lo_id = tree.parent_id
)
select jsonb_pretty(jsonb_agg(js))
from rels_from_children;
Can anybody help me with that?
Sysop: | Keyop |
---|---|
Location: | Huddersfield, West Yorkshire, UK |
Users: | 285 |
Nodes: | 16 (2 / 14) |
Uptime: | 69:36:12 |
Calls: | 6,488 |
Calls today: | 1 |
Files: | 12,096 |
Messages: | 5,275,382 |