Advanced SQL: CTE mastery
This course covers recursive CTEs in PostgreSQL, built against the community_comments table, the app's one self-referential model. It explains why a two-query thread loader stops working when reply depth is unknown. It uses Rails' with_recursive method, with an anchor and a recursive member joined by UNION ALL. It adds depth and path columns, guards against cycles that a foreign key does not prevent, and explains why recursive CTEs are always materialized. It closes with data-modifying CTEs and why this app's services do not use them.
What you'll learn
- ✓Writing recursive CTEs with Rails' with_recursive method
- ✓How the anchor and the recursive member run round by round
- ✓Computing depth and path inside the recursive member
- ✓Stopping cycles with a path-based guard
- ✓Why recursive CTEs are always materialized and how their cost grows
- ✓Data-modifying CTEs with RETURNING and why this app avoids them
The one thing WITH couldn't do
The junior course "ActiveRecord: CTE basics" closed its first lesson with a promise. It named what it left out: a CTE that references itself, WITH RECURSIVE, for tree- and graph-shaped data such as an org chart or "a comment thread's replies". It called itself "deliberately the non-recursive half".…
Read the full lesson free →Curriculum
7 lessonsThe one thing WITH couldn't do
Introduces Community::Comment as the app's only self-referential table and why a two-level thread needs no recursion.
with_recursive: Rails' own real method, verified
Builds a recursive query with Rails' with_recursive method, explaining the anchor, the recursive member and each round.
Depth and path: what the recursive term carries forward
Adds depth and path columns computed by the recursive member, one round at a time, with no Ruby processing.
The cycle a foreign key doesn't prevent
Shows that a parent_id foreign key allows cycles and stops them with a path-based guard instead of a round limit.
Always materialized: what RECURSIVE changes
Explains why recursive CTEs are always materialized in PostgreSQL and how their cost grows with the number of rounds.
A CTE that writes, and why this app doesn't reach for one
Demonstrates an UPDATE with RETURNING inside a CTE and why this app's services keep writes in Active Record.
Putting it together: one query, any depth, no loop
Combines anchor, recursive member, depth, path and cycle guard into one query that reads a thread of any depth.