How do you build a breadcrumb path column in a recursive CTE over a category tree?
answer
- depth says how far, this says which way
- one more column carried through the recursion
- append to the previous row's value
- watch the declared width of the anchor's expression
- sorting on it gives depth-first output
basics
~20 sConcatenate in the recursive member: the parent row's accumulated path, a delimiter, then the current row's name. Initialise the path in the anchor with an explicit CAST to a wide string type, and ORDER BY path to print the tree depth-first.
solid answer
~50 sThe path is an ordinary column carried through the recursion. The anchor seeds it with the starting node's label; the recursive member builds the child's value from the CTE row's path plus a delimiter plus the child's own label — `t.path || ' > ' || c.name`. Two details bite people. First, **cast the anchor expression** to a wide string type. Several engines derive the recursive column's data type and length from the anchor member alone, so an unwidened `name` column can silently truncate the accumulated path a few levels down. Second, **pick a delimiter that cannot occur in the data** — `>` inside a category name breaks any later parsing, and a `/`-delimited id path with the delimiter on both ends (`/1/7/`) makes containment tests unambiguous. Sorting by the path gives depth-first order, each node directly under its parent. If the path is built from numeric ids, zero-pad them so string ordering matches numeric ordering.
code
sql · 14 linesWITH RECURSIVE tree (id, name, parent_id, path) AS (
SELECT c.id, c.name, c.parent_id,
CAST(c.name AS VARCHAR(1000)) -- widen in the anchor
FROM categories c
WHERE c.parent_id IS NULL
UNION ALL
SELECT c.id, c.name, c.parent_id,
t.path || ' > ' || c.name
FROM categories c
JOIN tree t ON c.parent_id = t.id
)
SELECT id, name, path
FROM tree
ORDER BY path; -- depth-first outputgo deeper
Be able to write the concatenation: the previous row's path, a separator, then this row's name, with the path seeded in the anchor member. Knowing that ordering by the path prints the tree depth-first is a good extra.
Explain the two traps you would hit in practice — the anchor's declared type and width fixing the whole column, and a delimiter that collides with real data — and say why an id path is usually wrapped in delimiters on both ends.
Show that you distinguish a display path from a control path: breadcrumbs for humans, an id path as the visited-set for cycle detection and subtree tests, with sort-key padding handled deliberately rather than discovered in production.
Decide whether the path should be computed on every read at all. A materialised path column maintained on write changes the cost profile of the whole hierarchy and is a schema commitment, not a query trick.
## Why a path column A depth counter tells you how far a node is from the start; it does not tell you *which way* you came. A path column records the whole route: `Electronics > Phones > Cases`, or `/1/7/23/`. Two very different needs are served by it. - **Display**: a breadcrumb trail for a UI, or a sort key that prints the tree in the order a human reads it. - **Control**: a record of the nodes already visited, which is what a cycle guard tests against. Both are built the same way; only the payload differs — human-readable names for breadcrumbs, ids for cycle checks. ## Building it ```sql WITH RECURSIVE tree (id, name, parent_id, path) AS ( SELECT c.id, c.name, c.parent_id, CAST(c.name AS VARCHAR(1000)) FROM categories c WHERE c.parent_id IS NULL UNION ALL SELECT c.id, c.name, c.parent_id, t.path || ' > ' || c.name FROM categories c JOIN tree t ON c.parent_id = t.id ) SELECT id, name, path FROM tree ORDER BY path; ``` As with a depth counter, the accumulated value is read from the **CTE reference** (`t.path`), the newly appended piece from the base table (`c.name`). Each iteration appends exactly one segment, so a row's path always spells its own chain from the anchor. `||` is the standard SQL string concatenation operator. MySQL uses `CONCAT()` for this by default and SQL Server uses `+`, so this line is one of the least portable in the query. ## The type and width trap The anchor's `CAST(... AS VARCHAR(1000))` is not decoration. In a recursive CTE the column types must be consistent across both members, and some engines take the type — including the declared length — from the anchor member and coerce the recursive member's value to it. If your anchor selects the bare `name` column from a `VARCHAR(50)`, the path column can end up `VARCHAR(50)` and every deeper level is truncated or errors, depending on the engine. Casting the anchor expression to a generously sized string type is the standard fix and costs nothing. ## Choosing a delimiter For breadcrumbs the delimiter is cosmetic until someone tries to split the string back apart, at which point a category literally named `Phones > Cases` ruins the day. If the path will ever be parsed, use a character the data cannot contain, or build the path from ids rather than names. For id paths, wrap the delimiter around both ends: `/1/7/23/`. That makes membership tests exact — searching for `/7/` cannot accidentally match the `7` inside `/17/`. Half-delimited paths are the classic source of false positives in cycle guards. ## Ordering by the path `ORDER BY path` produces depth-first output: each node appears immediately after its parent and before its parent's next sibling, which is exactly how an indented tree reads. Combine it with the depth column for indentation and you have a printable org chart in one query. Be careful when the path is built from numbers. String comparison puts `'/10/'` before `'/2/'`, so a numeric id path sorts in an order that looks random to a user. Zero-pad the ids to a fixed width — `LPAD(CAST(c.id AS VARCHAR(10)), 10, '0')` where the engine offers `LPAD` — or accept the ordering as an opaque grouping key and sort the display by a name path instead. ## Arrays and the SEARCH clause Where the engine supports array types, an array path (append the id with an array concatenation) avoids the delimiter question entirely and makes membership a direct element test rather than substring matching. That is a dialect feature, so the string approach remains the portable default. The SQL standard also defines a `SEARCH` clause for recursive queries — `SEARCH DEPTH FIRST BY id SET ordercol` or `SEARCH BREADTH FIRST BY id SET ordercol` — which asks the engine to add an ordering column implementing exactly this traversal order, so you can `ORDER BY ordercol` without hand-rolling a sort key. Support varies by engine (PostgreSQL implements it from version 14); check yours before relying on it, and keep the manual path as the portable fallback. ## Common mistakes Appending to the base table's column instead of the CTE's, which produces a one-segment path on every row. Omitting the anchor cast and getting silent truncation. Using a delimiter that occurs in the data. Sorting numeric id paths lexicographically and being surprised. And building an expensive full-name path when the actual requirement was a cheap visited-set for cycle detection — the two look alike but only one needs to be readable.
- Why does the anchor member usually CAST the path expression to a wide string type?A recursive CTE's columns must have one consistent type across both members, and some engines infer that type — length included — from the anchor alone. Seeding the path with a bare `VARCHAR(50)` name column can make the whole path column 50 characters wide, so deeper levels are truncated or the query errors. Casting the anchor expression to a generous width avoids it.
- You ordered by an id-based path like /1/10/ and the output looks shuffled. Why?Paths are strings, so they compare character by character: `/1/10/` sorts before `/1/2/` because `'1' < '2'` at that position. Zero-pad the ids to a fixed width when building the path so lexicographic order matches numeric order, or sort the display by a name-based path and keep the id path purely as a key.
- When would you accumulate ids rather than names in the path?Whenever the path is machinery rather than display: cycle guards, subtree membership tests, and stable sort keys. Ids are fixed-width-ish, immutable, and free of delimiter collisions, whereas names are long, may contain the delimiter, and change under you. Build a readable name path only when a human is going to read it.
saying these in an interview costs you the question
- Appends to the base table's column instead of the CTE's path
- Skips the anchor CAST and blames the engine for truncation
- Uses a delimiter that can appear inside a category name
- Expects numeric id paths to sort numerically as strings
- Thinks ORDER BY depth alone prints an indented tree