0

What I need
I need select all ids or names from table "category" (all chidrens) until category_id is 0 (main category).

Example On web there is product with category tree.

web_category

In table "category" I have parent category id as 0, and other children categories ids are defined by numbers as this.

enter image description here

How can I in sql show all categories tree in one field? For example.

Úvod > Bydlení > Barvy a Laky > Vnitřní barvy

What is the simplest way to do that?

DBMS = PostgreSQL ver. 10.9.

Thank you very much.

BR.

Edited, updated, for ids main category id is not present.

By Akina solution.

with recursive

cte as(
select category_id,category_parent,category_name::text
from s_category
where category_parent = 0
union all
select
s_category.category_id,
s_category.category_parent,
concat(cte.category_name, ' > ', s_category.category_name)
from s_category,cte
where cte.category_id = s_category.category_parent
),

cte2 as(
select category_id,category_parent,category_name::text
from s_category
where category_parent = 0
union all
select
s_category.category_id,
s_category.category_parent,
concat(cte2.category_id, ' : ', s_category.category_id)
from s_category,cte2
where cte2.category_id = s_category.category_parent
)

select
cte.category_name as tree_category,
cte2.category_id as tree_id,
s_product.product_id as itemID,
s_product.product_id as itemGroupID,
product_shop_id as productCode,
product_ean as ean,
product_name as title,
product_short_label as description,
product_url as link,
filename as image,
price_tax as price,
price_rec as priceOriginal,
producer_name as brand

from
s_product

left join s_product_image on s_product.product_id = s_product_image.product_id
left join s_pricelist_generated_lists on s_product.product_id = s_pricelist_generated_lists.product_id
left join s_producer on s_product.producer_id = s_producer.producer_id
left join cte on s_product.category_id = cte.category_id
left join cte2 on s_product.category_id = cte2.category_id

where
s_product.product_id = '133471'
1

Example.

Structure and data:

CREATE TABLE tree (id INT, parent INT, name VARCHAR);

INSERT INTO tree VALUES
(1,0,'root'),
(2,1,'branch 1'),
(3,1,'branch 2'),
(4,2,'branch 3'),
(5,2,'branch 4'),
(6,4,'branch 5'),
(7,6,'branch 6'),
(8,3,'leaf 1'),
(9,5,'leaf 2'),
(10,7,'leaf 3');

Query:

WITH RECURSIVE cte AS ( SELECT t1.id, t1.parent, t1.name
                        FROM tree t1
                        LEFT JOIN tree t2 ON t1.id = t2.parent
                        WHERE t2.parent is null
                        UNION ALL
                        SELECT t1.id, t1.parent, CONCAT(t1.name, ' > ', t2.name)
                        FROM tree t1
                        JOIN cte t2 ON t1.id = t2.parent 
                      )
SELECT name
FROM cte
WHERE parent = 0;

Output:

                    name
---------------------------------------------------------
root > branch 2 > leaf 1
root > branch 1 > branch 4 > leaf 2
root > branch 1 > branch 3 > branch 5 > branch 6 > leaf 3

fiddle


UPDATE:

WITH recursive cte AS ( SELECT t1.category_id, t1.category_parent,    t1.category_name::text
                    FROM s_category t1
                    LEFT JOIN s_category t2 ON t1.category_id = t2.category_parent
                    WHERE t2.category_parent is null
                    UNION ALL
                    SELECT t1.category_id, t1.category_parent,     CONCAT(t1.category_name, ' > ', t2.category_name)
                    FROM s_category t1
                    JOIN cte t2 ON t1.category_id = t2.category_parent 
                  )
select 

cte.category_name AS category,
s_product.product_id as itemID,
s_product.product_id as itemGroupID,
product_shop_id as productCode,
product_ean as ean,
product_name as title,
product_short_label as description,
product_url as link,
filename as image,
price_tax as price,
price_rec as priceOriginal,
producer_name as brand,
-- as category,
s_category.category_id as categoryID
-- as hierarchy,
-- as params,
-- as name,
-- as value,
-- as labels

from

public.s_product
left join s_product_image on s_product.product_id =             s_product_image.product_id
left join s_pricelist_generated_lists on s_product.product_id = s_pricelist_generated_lists.product_id
left join cte on s_product.category_id = cte.category_id
left join s_producer on s_product.producer_id = s_producer.producer_id

where

s_product.product_id='133471'
and s_pricelist_generate

UPDATE2:

Alternative CTE - producing URL for each node.

WITH RECURSIVE 
cte AS ( SELECT id, parent, name
         FROM tree
         WHERE parent = 0
       UNION ALL
         SELECT tree.id, tree.parent, CONCAT(cte.name, ' > ', tree.name)
         FROM tree, cte
         WHERE cte.id = tree.parent
       )
  • Thank you, now it is working. I updated my code in my question. Last question. When I want to do it same thing, but for ids (for example 6439 >6464 > 6669), is new cte needed, or can I do it just little modificatin in existing cte or in select? Thank you. – Stanislav Hosek Jul 19 at 13:21
  • Add one more field which collects IDs by the same way. – Akina Jul 19 at 19:18
  • I did, but it shows me 6464:6669 instead 6439:6464:6669, main category is missing. And how can I display first value 'Úvod' in first cte? Question/code updated above. Thanks. – Stanislav Hosek Jul 22 at 21:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.