Recursive query in PostgreSQL | Code Factory

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;

--

--

--

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Install the Kubernetes Dashboard UI

How to launch an instance and attach the volume through CLI

The ultimate guide (for both dummies and pros) how to setup a DECENT Mining Witness Node

KYVE Network incentivisioned-testnet

Breaking up with Flutter, getting along with PWA

Using Regex in Python

The road to becoming a self-taught developer in Singapore

K3s — A better K8s?

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Code Factory

Code Factory

More from Medium

Understanding Docker Architecture

Understanding Docker Architecture

How To…Increasing your Database Performance

Postgres UNNEST cheat sheet for bulk operations

9 Insanely Helpful Kafka Commands Every Developer Must Know