Advertisement
Guest User

Untitled

a guest
Sep 12th, 2019
115
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. WITH RECURSIVE CategoryTree("id", "categoryName", "prevId", "level") AS (
  2.   SELECT public."productCategories"."id", public."productCategories"."categoryName", public."productCategories"."prevId", 1 AS level
  3.     FROM public."productCategories"
  4.  
  5.   WHERE id = 13
  6.    
  7.   UNION ALL
  8.  
  9.   select categories."id", categories."categoryName", categories."prevId", categoryTree."level" + 1
  10.     FROM public."productCategories" categories
  11.       JOIN categoryTree ON categories."id" = categoryTree."prevId"
  12. )
  13.  
  14. SELECT array_to_string(array_agg(distinct "id"), '/') as "categoryTreeId", string_agg("categoryName", '/') as "categoryTreeName"
  15.    FROM (
  16.     SELECT "id", "categoryName", "level"
  17.       FROM categoryTree
  18.       ORDER BY "level" DESC
  19.     ) as result
  20. ;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement