examples · sql · 13 / 13 · roadmap

13. WITH RECURSIVE (roadmap)

roadmap

Recursive CTEs (WITH RECURSIVE ...) are not yet supported. For hierarchical walks (org charts, citation chains, dependency graphs) - which is what RECURSIVE is most often used for - use the graph endpoints instead. They're purpose-built for that workload.

the classic case

You'd write:

WITH RECURSIVE descendants AS (
  SELECT id, manager_id FROM hr.employees WHERE id = 'ceo'
  UNION ALL
  SELECT e.id, e.manager_id
    FROM hr.employees e
    INNER JOIN descendants d ON e.manager_id = d.id
)
SELECT * FROM descendants

Today this returns 400 unsupported_sql.

workaround - graph BFS

Declare the parent column as a relation on the schema, then walk the edge with depth-bounded BFS. The graph endpoints are purpose-built for this - no need to express it in SQL.

# A hierarchy walk via the graph endpoint instead of WITH RECURSIVE.
# Assumes the schema declares manager_id as a [[relations]] block:
#
#   [[relations]]
#   name          = "reports_to"
#   from_col      = "manager_id"
#   bidirectional = true
#
#   [relations.target]
#   namespace = "hr"
#   table     = "employees"
#   pk        = "id"

curl "https://$OC_HOST/v1/tenants/$OC_TENANT/graph/hr.employees/bfs?rel=reports_to&pk=ceo&max_depth=10" \
  -H "Authorization: Bearer $OC_TOKEN"

See Graph reference for BFS, shortest path, k-shortest, and the other 16 graph algorithms. Schema reference → relations covers how to declare the relation.

why graph is the right tool

BFS has a built-in max_depth guard - WITH RECURSIVE on cyclic data needs an explicit depth cap that's easy to forget.
Forward + reverse traversal are both indexed (bidirectional relations) - "who reports to X" and "who does Y report to" are equally fast.
For weighted paths or shortest-path, you can swap BFS for Dijkstra or k-shortest without rewriting the schema.