Truy vấn cây danh mục nhiều cấp bằng recursive CTE như thế nào? Chống vòng lặp vô hạn ra sao?

WITH RECURSIVE gồm hai phần nối bằng UNION ALL: truy vấn neo (anchor) lấy điểm xuất phát, và truy vấn đệ quy tham chiếu ngược lại chính CTE.

WITH RECURSIVE tree AS (
  -- anchor: node goc
  SELECT id, parent_id, name, 1 AS depth,
         ARRAY[id] AS path
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  -- recursive: noi con vao ket qua da co
  SELECT c.id, c.parent_id, c.name, t.depth + 1,
         t.path || c.id
  FROM categories c
  JOIN tree t ON c.parent_id = t.id
  WHERE NOT c.id = ANY(t.path)   -- chan vong lap
    AND t.depth < 10             -- chan do sau
)
SELECT * FROM tree ORDER BY path;

Hai cơ chế chống vòng lặp:
- Mảng path: tích luỹ các id đã đi qua, loại node đã xuất hiện. Đây cũng là cột dùng để ORDER BY ra đúng thứ tự cây.
- Chặn depth: bảo hiểm cứng, tránh treo truy vấn khi dữ liệu bị lỗi tham chiếu vòng.

Lưu ý:
- UNION ALL là mặc định; dùng UNION sẽ khử trùng lặp ở mỗi vòng — chậm hơn nhưng cũng chặn được chu trình đơn giản.
- Đổi anchor thành WHERE id = :leaf và join ngược (t.parent_id = c.id) để đi lên tìm breadcrumb.
- Nếu cây đọc nhiều ghi ít, cân nhắc lưu sẵn materialized path hoặc closure table thay vì đệ quy mỗi lần đọc.