Three approaches to fetching hierarchical data from a self-referencing table: multiple merged queries, recursive CTEs, and nested queries.
1. Write multiple queries, and then merge the results.
- This was actually happening with Prisma
2. Using recursive CTEs
WITH RECURSIVE ModuleHierarchy AS (
-- Base case: select root modules
SELECT
id,
module_name,
parent_id,
icon,
"path",
display_order,
CAST(id AS TEXT) AS recursive_path
FROM
hrm.modules
WHERE
parent_id IS null
and modules.active_status = true
UNION ALL
-- Recursive case: join with ModuleHierarchy to find child modules
SELECT
m.id,
m.module_name,
m.parent_id,
m.icon,
m."path",
m.display_order,
CONCAT(h.recursive_path, '->', m.id) AS recursive_path
FROM
hrm.modules m
INNER JOIN
ModuleHierarchy h ON m.parent_id = h.id
where
m.active_status = true
)
SELECT
mh1.id,
mh1.module_name,
mh1.icon,
mh1."path",
mh1.display_order,
COALESCE(
JSON_AGG(
JSON_BUILD_OBJECT(
'id', mh2.id,
'module_name', mh2.module_name,
'icon', mh2.icon,
'path', mh2."path",
'child_modules', COALESCE(
(
SELECT
JSON_AGG(
JSON_BUILD_OBJECT(
'id', mh3.id,
'module_name', mh3.module_name,
'icon', mh3.icon,
'path', mh3."path"
)
)
FROM
ModuleHierarchy mh3
WHERE
mh3.recursive_path LIKE CONCAT(mh2.recursive_path, '->%')
),
'[]'
)
)
) FILTER (WHERE mh2.id IS NOT NULL),
'[]'
) AS child_modules,
mh1.recursive_path
FROM
ModuleHierarchy mh1
LEFT JOIN
ModuleHierarchy mh2 ON mh2.parent_id = mh1.id
WHERE
mh1.parent_id IS NULL
GROUP BY
mh1.id, mh1.module_name, mh1.icon, mh1.recursive_path, mh1."path", mh1.display_order
order by
mh1.display_order, mh1.module_name- It's a good fit when the data is very dynamic
- If data is not frequently changed, can add materialized paths - basically save the path hierarchy in a column and then use that.
- CTEs - Common table expressions - it creates a temporary kind of table on which complex queries can be performed.
- Recursive CTEs - For this you give a base or root query, and then recursive query which will be called with the old result, something like a reducer in JavaScript
- There are multiple strategies to create the recursion, like path, sets, etc. having tradeoffs
3. Using Nested queries
SELECT
m.id as id,
m.module_name,
m.icon,
m."path",
m.display_order,
COALESCE(
JSON_AGG(
JSON_BUILD_OBJECT(
'id', sub.id,
'module_name', sub.module_name,
'icon', sub.icon,
'path', sub."path",
'child_modules', COALESCE(
(
SELECT
JSON_AGG(
JSON_BUILD_OBJECT(
'id', sub2.id,
'module_name', sub2.module_name,
'icon', sub2.icon,
'path', sub2."path"
)
)
from modules sub2
where sub2.parent_id = sub.id
group by sub2.display_order
order by sub2.display_order
),
'[]'
)
)
)
FILTER (WHERE sub.id IS NOT NULL),
'[]'
) AS child_modules
FROM
modules m
join
modules sub on m.id = sub.parent_id
where
m.parent_id is null
GROUP BY
m.parent_id, m.id, m.module_name, m.icon, m."path", m.display_order, sub.display_order
order by
m.display_order, sub.display_order- No overhead of creating multiple joins for CTEs
- Becomes slower and cumbersome to manage with adding more nested levels