Recursive query in PostgreSQL | Code Factory


Donate : Link

Medium Blog : Link

Applications : 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;

Advertisement

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s