Common Table Expressions — Aquilia Documentation

Comprehensive guide and documentation for Common Table Expressions in the Aquilia framework. View API reference, examples, and implementation patterns.

Docs / Models / Common Table Expressions Common Table Expressions Named subqueries defined at the start of a statement — compose readable, reusable query fragments. v1.3.3+ Concept A Common Table Expression (CTE) is a named, temporary result set defined with a WITH name AS (SELECT ...) clause that precedes the main query. The main query can then reference the CTE by name as if it were a table. CTEs excel at: Breaking complex queries into readable steps — each CTE is a named, focused subquery Reusing subquery results — reference the same CTE multiple times in the main query Post-processing window function results — filter on a windowed annotation (impossible in a single SELECT) Analytics pipelines — chain filtering, ranking, and aggregation as separate named stages Performance note: Most databases materialise CTEs once (PostgreSQL < 12 always; PostgreSQL 12+ optimises unless MATERIALIZED is specified). On SQLite and MySQL 8.0+, the optimiser may inline CTEs. Measure with .explain() if performance matters. Imports from aquilia.models import CTE, CTEReference, CTECol # CTE objects are also created via the QuerySet .cte() method — no direct instantiation needed Basic Non-Recursive CTE Call .cte(name) on any QuerySet to create a named CTE, then pass it to .with_cte() on the outer query. The outer query can then reference the CTE name as a table. from aquilia.models import F # Step 1: Define the CTE queryset active_users_qs = User.objects.filter(is_active=True, verified=True) # Step 2: Name it as a CTE active_users = active_users_qs.cte('active_users') # Step 3: Use it in an outer query # Note: the outer query selects FROM the CTE name as a table result = await ( User.objects .with_cte(active_users) .filter(role='admin') .order('-created_at') .all() ) # Generates: # WITH "active_users" AS ( # SELECT * FROM "users" WHERE "is_active" = ? AND "verified" = ? # ) # SELECT * FROM "users" WHERE "role" = ? ORDER BY "created_at" DESC Multiple CTEs Pass multiple CTEs to .with_cte() in a single call, or chain calls additively. CTEs are rendered in registration order. # Analytics pipeline: filter → annotate → join via CTEs # CTE 1: active users with post count active_qs = User.objects.filter(is_active=True) active = active_qs.cte('active_users') # CTE 2: top posts (last 30 days) from datetime import datetime, timedelta cutoff = datetime.utcnow() - timedelta(days=30) top_posts_qs = Post.objects.filter(created_at__gte=cutoff).order('-likes') top_posts = top_posts_qs.cte('recent_top_posts') # Main query referencing both result = await ( User.objects .with_cte(active, top_posts) # both CTEs in one call .filter(is_active=True) .all() ) # Generates: # WITH "active_users" AS (...), # "recent_top_posts" AS (...) # SELECT ... Referencing CTE Columns Use cte.col("column_name") to get a CTECol expression that renders as "cte_name"."column". Use this in filter expressions or annotations that reference the CTE. from aquilia.models import CTE, F ranked_qs = Post.objects.annotate( rn=Window(RowNumber(), partition_by=['author_id'], order_by='-likes') ) ranked = ranked_qs.cte('ranked_posts') # Reference CTE column in outer query filter top3_col = ranked.col('rn') # → CTECol("ranked_posts", "rn") # Use in annotation or raw filter result = await ( Post.objects .with_cte(ranked) .filter(** ) # Filter on annotated window rank from CTE .all() ) Production Pattern: Rank + Filter The most common CTE pattern is computing a window function annotation in a CTE and then filtering on it in the outer query — bypassing the SQL restriction that window functions cannot appear in WHERE. from aquilia.models import Window, RowNumber, F # TOP-3 POSTS PER AUTHOR — using CTE to filter on window annotation # Inner queryset: annotate with row number within each author's partition ranked_qs = Post.objects.annotate( rn=Window( RowNumber(), partition_by=['author_id'], order_by='-likes', ) ) ranked = ranked_qs.cte('ranked_posts') # Outer query: select from the CTE, filter on rn top3 = await ( Post.objects .with_cte(ranked) .filter(rn__lte=3) # Now valid — rn is a column in the CTE .order('author_id', 'rn') .all() ) # → Up to 3 most-liked posts per author Parameter Ordering Aquilia guarantees correct parameter ordering: CTE bind values are prepended before the main query's annotation params and WHERE params. You should not need to manage this manually. # The generated parameterized query: # WITH "active_users" AS (SELECT ... WHERE "is_active" = ?) ← CTE param: True # SELECT * FROM "users" WHERE "role" = ? ← WHERE param: 'admin' # Final params: [True, 'admin'] (CTE params first, always) Backend Compatibility Backend Min Version Notes ))} Window Functions Recursive CTEs );

Go to Homepage