I have Categories stored in a single table.
Where there is no limit on number of childerns.
I want to fetch all the linked categories for the provided category:
Table name: categories
id parentId name path isLastLevel
1 0 Cat 1 Cat 1 0
2 1 Cat 2 Cat 1 > Cat 2 0
3 2 Cat 3 Cat 1 > Cat 2 > Cat 3 1
4 0 Cat A Cat A 0
5 4 Cat B Cat A > Cat B 1
Now I want to fetch all the hierarchy for id: 2 i.e Cat 2
Expected response:
[
{
id: 1,
parentId: 0,
name: Cat 1,
path: Cat 1,
isLastLevel: 0
},
{
id: 2,
parentId: 1,
name: Cat 2,
path: Cat 1 > Cat 2,
isLastLevel: 0
},
{
id: 3,
parentId: 2,
name: Cat 3,
path: Cat 1 > Cat 2 > Cat 3,
isLastLevel: 1
}
]
I am trying this query but its not giving me the parent records:
with recursive cte
(id, name, parentId)
AS
(
select id, name, parentId from categories
where parentId = 1
union all select c.id, c.name, c.parentId from categories c
inner join cte on c.parentId = cte.id
)
select * from cte;
I have also created an SQL playground:
