ActiveRecord: CTE basics
This course covers non-recursive Common Table Expressions in Postgres and how ActiveRecord builds them. It starts with the SQL a WITH clause produces, then moves to ActiveRecord::Relation#with and the injection warning in its doc comments for raw Arel.sql strings. It shows that defining a CTE has no effect until .from or .joins references it by name. Later lessons join a CTE built from grouped enrollment data, build one CTE from another, and use EXPLAIN to test the optimization fence claim on PostgreSQL 12 and later.
What you'll learn
- ✓How WITH names a subquery for the length of one statement
- ✓Building CTEs with ActiveRecord::Relation#with and the SQL injection risk of Arel.sql
- ✓Connecting a CTE to the query with .from or .joins
- ✓Joining against a CTE built from grouped data
- ✓Chaining CTEs and the order rule Postgres enforces
- ✓Reading EXPLAIN plans for inlined and MATERIALIZED CTEs
WITH: giving a subquery a name you can reuse
SQL: grouping & aggregates used GROUP BY with HAVING to keep only the groups above a threshold, such as courses with more than one enrollment. Written as a subquery, that result is computed once and thrown away as soon as the outer query finishes. Postgres can compute the same subquery and give it…
Read the full lesson free →Curriculum
7 lessonsWITH: giving a subquery a name you can reuse
How WITH gives a subquery a name that exists for one statement and can be reused.
ActiveRecord::Relation#with: Rails' method for building a CTE
How ActiveRecord::Relation#with builds a WITH clause from a hash of names and relations, plus its injection warning.
The trap: defining a CTE with .with doesn't mean anything references it
Why a CTE defined with .with has no effect until .from or .joins references it by name.
Joining a CTE: grouped enrollment data from this app's tables
Joining courses against a CTE of grouped enrollment data using a raw SQL string in .joins.
One CTE built from another
Building one CTE from an earlier one with .from, and why Postgres enforces their order.
The optimization fence myth: true before Postgres 12, false by default now
Using EXPLAIN to show that PostgreSQL 12 and later inline single-reference CTEs, and what MATERIALIZED changes.
Putting it together: a name, defined once, read where it's used
A recap of the model: a name defined once, connected with .from or .joins, and read where used.