Follow a hierarchy

← SQL examples

Expand an organization chart from one employee. A recursive common table expression, or CTE, repeats a query using the previous step’s results.

Before you run it: Limits are 100 levels and one million rows. The path supports one recursive CTE with UNION ALL; the restrictions below still apply.

Follow the exampleCopy Anchor Link

1. Create the employee sample.
2. Choose the starting employee.
3. Run the UNION ALL query.
4. Inspect descendants and stop conditions.

Load a three-person hierarchyCopy Anchor Link

Use a disposable test database and the tenant-scoped connection from the example setup notes. A missing manager marks the root; each other row stores its manager's ID.

curl --fail-with-body -X POST "https://$OC_HOST/v1/tenants/$OC_TENANT/schemas" \
  -H "Authorization: Bearer $OC_TOKEN" -H "Content-Type: text/plain" \
  --data-binary @- <<'TOML'
namespace = "hr"
table = "employees"
primary_key = ["id"]
[[columns]]
name = "id"
ty = "str"
required = true
[[columns]]
name = "manager_id"
ty = "str"
[[relations]]
name = "reports_to"
from_col = "manager_id"
bidirectional = true
target = { namespace = "hr", table = "employees", pk = "id" }
TOML

curl --fail-with-body -X POST "https://$OC_HOST/v1/tenants/$OC_TENANT/rows/hr.employees/_batch" \
  -H "Authorization: Bearer $OC_TOKEN" -H "Content-Type: application/json" \
  -d '[
    {"id":"ceo"},
    {"id":"lead","manager_id":"ceo"},
    {"id":"engineer","manager_id":"lead"}
  ]'

The recursive SQL below starts with ceo, finds lead, then finds engineer. It keeps the starting row too. Those three IDs are the expected result; no ordering is promised without an outer ORDER BY.

Supported operationsCopy Anchor Link

WITH and WITH RECURSIVE use the SQL endpoint. A recursive query starts with seed rows and repeats its step until no new rows remain. The sections below show the supported form and its bounds.

Walk the employee hierarchyCopy Anchor Link

Walk an org chart down from one root. This runs as written:

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

The response contains the rows reachable from the starting row. Access checks apply to the tables in both the seed and recursive step; a table you cannot read causes the query to fail.

Keep the traversal boundedCopy Anchor Link

• UNION ALL between the seed and step arms. UNION (distinct) is refused - deduplicating would mean hashing every intermediate row.
• Exactly one CTE in a statement that uses RECURSIVE; chaining a second binding off the recursive one is refused.
• No per-CTE column list (WITH RECURSIVE t(a, b) AS ...).
• No aggregate over the recursive relation in the outer projection - SELECT COUNT(*) FROM t is refused, so count client-side. (Over a plain CTE, COUNT(*) does fold - the asymmetry is deliberate.)
• Two caps bound the walk: a depth cap of 100 iterations, and a separate cap of 1,000,000 accumulated rows. Cyclic or wide-branching data hits one of them and comes back as a 400 naming the cap it hit - it will not spin or exhaust the engine. Add a visited-set predicate to make such a walk terminate with rows instead.
• Filtering and projecting the recursive relation in the outer query works: SELECT id FROM descendants WHERE id <> 'ceo'.

Use graph traversal insteadCopy Anchor Link

Graph traversal is another way to follow stored relationships, but direction matters. The reports_to relation below points from an employee to a manager. Its forward BFS walks up the organization; it is not a replacement for the SQL descendant query above. Starting that forward walk at ceo returns no descendants in this sample dataset. Use reverse relationship lookup when finding the people who report to a manager.

# 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.

Choose SQL or graph traversalCopy Anchor Link

• BFS takes max_depth as an explicit per-request argument. WITH RECURSIVE is capped too, but by fixed engine limits you tune the query around rather than pass in.
• 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.

Expected resultCopy Anchor Link

Each round adds the next level until there are no new rows or an execution limit is reached.

ContinueCopy Anchor Link

Continue to explore nearby connections.