CODEWITHSUNDEEP
sqlpostgresqljoinhierarchical-data
Praveen Kumar
Praveen Kumar

Reputation: 1539

postgresql WITH RECURSIVE query to get category and subcategories

I have a table as below

enter image description here

Using the WITH RECURSIVE statement i am able to get the depth of the table

WITH RECURSIVE category_tree(id, name, depth) AS (
  SELECT id, name, ARRAY[id]
  FROM category
  WHERE parentid IS NULL
UNION ALL
  SELECT category.id, category.name, depth || category.id
  FROM category_tree
  JOIN category ON category.parentid=category_tree.id
  WHERE NOT category.id = ANY(depth)
)
SELECT * FROM category_tree ORDER BY id;

How should i change the query such that i can get the output as below (till the nth level of the tree)?

enter image description here

I intend to use the above result to generate a site map like below:

enter image description here

I am currently trying to use the LEFT OUTER JOIN with WITH RECURSIVE statement but i am unable to find how ? Can some one please be able to guide?

Upvotes: 5

Views: 2394

Answers (1)

trincot
trincot

Reputation: 351029

For the output format you describe, I would not go for a recursive query. Recursive queries produce rows that you would need to pivot to get to your format. In a pivot query you need to specify which columns you will have. All in all, that becomes a long query, and for an output with just 4 or 5 columns, the following will be more straightforward:

SELECT    c0.name  AS root_name,
          c1.name  AS down1_name,
          c2.name  AS down2_name,
          c3.name  AS down3_name,
          c4.name  AS down4_name,
          c5.name  AS down5_name
FROM      category c0
LEFT JOIN category c1 ON c1.parentid = c0.id
LEFT JOIN category c2 ON c2.parentid = c1.id
LEFT JOIN category c3 ON c3.parentid = c2.id
LEFT JOIN category c4 ON c4.parentid = c3.id
LEFT JOIN category c5 ON c5.parentid = c4.id
WHERE     c0.parentid IS NULL
ORDER BY  c0.id, c1.id, c2.id, c3.id, c4.id, c5.id

Upvotes: 3

Related Questions