Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS product_categories;
DROP TABLE IF EXISTS products;
CREATE TEMP TABLE categories
as
select *
from (
VALUES
(1 :: int, 'a' :: VARCHAR, null),
(2 :: int, 'b' :: VARCHAR, null),
(3 :: int, 'c' :: VARCHAR, null),
(4 :: int, 'aa' :: VARCHAR, 1),
(5 :: int, 'aa' :: VARCHAR, 1),
(6 :: int, 'aaa' :: VARCHAR, 4),
(7 :: int, 'bb' :: VARCHAR, 2),
(8 :: int, 'bbb' :: VARCHAR, 7)
) as t (id, name, parent_id);
CREATE TEMP TABLE products
as
select *
from (
VALUES
(1 :: int, 'a' :: VARCHAR),
(2 :: int, 'b' :: VARCHAR),
(3 :: int, 'c' :: VARCHAR)
) as t (id, name);
CREATE TEMP TABLE product_categories
as
select *
from (
VALUES
(1 :: int, 3 :: int, 6 :: int),
(2 :: int, 3 :: int, 6 :: int),
(3 :: int, 1 :: int, 3 :: int)
) as t (id, product_id, category_id);
-- WITH RECURSIVE list_categories(id, name, childs) AS (
WITH RECURSIVE list_categories(id, name) AS (
SELECT
parent_category.id,
parent_category.name
-- parent_category.name,
-- json_agg(child_categories) filter (where child_categories.id is not null) as childs
from categories parent_category
left outer join categories child_categories on child_categories.parent_id = parent_category.id
where parent_category.parent_id isnull
group by parent_category.id, parent_category.name
UNION ALL
SELECT
child_categories.id,
child_categories.name
-- child_categories.name,
-- array_agg(child_categories) as childs
from categories child_categories
inner join list_categories on child_categories.parent_id = list_categories.id
left outer join categories child_child_categories on child_child_categories.parent_id = child_categories.id
group by child_categories.id, child_categories.name
)
SELECT *
from list_categories;
WITH RECURSIVE list_categories(id, name, path) AS (
SELECT
parent_category.id,
parent_category.name,
parent_category.id || '/' as path
from categories parent_category
left outer join categories child_categories on child_categories.parent_id = parent_category.id
where parent_category.parent_id isnull
group by parent_category.id, parent_category.name
UNION ALL
SELECT
child_categories.id,
child_categories.name,
list_categories.path || child_categories.id || '/'
from categories child_categories
inner join list_categories on child_categories.parent_id = list_categories.id
left outer join categories child_child_categories on child_child_categories.parent_id = child_categories.id
)
SELECT *
from list_categories;
select ARRAY [] :: integer []
select '123' || '/'
SELECT
parent_category.id,
parent_category.name,
json_agg(child_categories.*) as childs
from categories parent_category
left outer join categories child_categories on child_categories.parent_id = parent_category.id
where parent_category.parent_id isnull
group by parent_category.id, parent_category.name
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.