Recursive query in PostgreSQL | Code Factory

Code Factory

Follow

1 min read

·

Apr 4, 2020

--

Listen

Share

Reference Link : Link

Donate : Link

WITH RECURSIVE menu_tree AS (
 SELECT mm.id,
  mm.menu_name,
  mm.menu_action,
  mm.parent_menu_id,
  mm.menu_order,
  1 AS level,
  ARRAY[mm.id] AS path_info
 FROM menu_master mm
 WHERE mm.parent_menu_id = 0
 UNION ALL
 SELECT c.id,
  c.menu_name,
  c.menu_action,
  c.parent_menu_id,
  c.menu_order,
  p.level + 1,
  (p.path_info || c.menu_order::bigint) || c.id
 FROM menu_master c
 JOIN menu_tree p ON c.parent_menu_id = p.id
)
SELECT mt.id,
 mt.menu_name,
 mt.menu_action,
 mt.parent_menu_id,
 mt.menu_order,
 mt.level,
 mt.path_info
FROM menu_tree mt
ORDER BY mt.path_info;