Recursive CTEs — Aquilia Documentation
Comprehensive guide and documentation for Recursive CTEs in the Aquilia framework. View API reference, examples, and implementation patterns.
Docs / Models / Recursive CTEs Recursive CTEs Traverse hierarchical and graph data in pure SQL — folder trees, org charts, dependency graphs. v1.3.3+ Concept A recursive CTE uses the WITH RECURSIVE keyword and consists of two parts joined by UNION ALL (or UNION): Anchor term — the base query, run once, producing initial rows (e.g., root nodes) Recursive term — references the CTE itself to join against previous iteration results, expanding outward The database engine iterates until the recursive term produces no new rows. Warning: Without a termination condition, recursive CTEs can loop infinitely on cyclic graphs. Use UNION (not UNION ALL) to deduplicate and prevent cycles, or add a depth counter and filter it in the anchor or application layer. API: Q.recursive_cte() Parameter Type Description ))} .recursive_cte() returns a new QuerySet that selects FROM the named CTE table and carries the RecursiveCTE in its WITH RECURSIVE clause. Terminal methods (.all(), .first()) execute the full query. Example: Folder Tree Traversal A self-referential Folder model with a parent_id nullable FK to itself. class Folder(Model): table = "folders" name = CharField(max_length=255) parent_id = IntegerField(null=True) # FK to folders.id # Fetch entire subtree rooted at parent_id IS NULL (top-level) all_folders = await Folder.objects.recursive_cte( name='folder_tree', anchor=lambda q: q.filter(parent_id__isnull=True), # Root nodes recursive=lambda cte: Folder.objects.filter( parent_id=cte.col('id') # Children of previous level ), union_all=True, # Trees have no cycles — UNION ALL is faster ).all() # Generates: # WITH RECURSIVE "folder_tree" AS ( # SELECT * FROM "folders" WHERE "parent_id" IS NULL -- anchor # UNION ALL # SELECT f.* FROM "folders" f # INNER JOIN "folder_tree" ft ON f."parent_id" = ft."id" -- recursive # ) # SELECT * FROM "folder_tree" Example: Org Chart (All Reports Under a Manager) class Employee(Model): table = "employees" name = CharField(max_length=255) manager_id = IntegerField(null=True) # FK to employees.id async def all_reports(manager_id: int) -> list[Employee]: """Return the manager and all their direct/indirect reports.""" return await Employee.objects.recursive_cte( name='reports', anchor=lambda q: q.filter(id=manager_id), # Start node recursive=lambda cte: Employee.objects.filter( manager_id=cte.col('id') ), union_all=True, ).all() Example: Dependency Graph (UNION for Cycle Safety) class Package(Model): table = "packages" name = CharField(max_length=255) class PackageDep(Model): table = "package_deps" package_id = IntegerField() depends_on_id = IntegerField() # Resolve all transitive dependencies of a given package async def transitive_deps(package_id: int) -> list[Package]: return await Package.objects.recursive_cte( name='dep_tree', anchor=lambda q: q.filter(id=package_id), recursive=lambda cte: Package.objects.filter( packagedep__package_id=cte.col('id') ), union_all=False, # UNION — deduplicates, prevents infinite loops on diamond deps ).all() UNION vs UNION ALL Setting SQL When to use ))} CTEReference and cte.col() Inside the recursive lambda, the argument is a CTEReference. Call .col("column") on it to produce a CTECol expression that renders as "cte_name"."column" in SQL. lambda cte: Folder.objects.filter(parent_id=cte.col('id')) # cte.col('id') → CTECol("folder_tree", "id") # Renders as: "folders"."parent_id" = "folder_tree"."id" Performance Considerations ℹ Index parent_id — the recursive join hits parent_id on every iteration. Missing index causes full table scan per level. ℹ Depth-limit via counter — add a depth annotation in the anchor (initialized to 0 via Value(0)) and increment in the recursive term to limit traversal depth. ⚠ Large result sets — recursive CTEs materialize all levels before returning. For very deep trees, combine with .limit() or use a depth counter. ✓ Use .explain() — await Folder.objects.recursive_cte(...).explain() shows the query plan including CTE materialization strategy. Backend Compatibility Backend Min Version Notes ))} Common Table Expressions Bulk Operations );
Go to Homepage