django-cte

You can include multiple CTEs in a single query. To avoid name collisions, assign each CTE a unique name using the name parameter in CTE(...) or CTE.recursive(...).

Important: When using multiple CTEs, you must pass all of them as positional arguments to with_cte(cte1, cte2, ..., select=...). A CTE can also reference another CTE defined earlier in the same query.

# Define first CTE with a custom name
rootmap = CTE.recursive(make_root_mapping, name="rootmap")

# Define second CTE that references the first
totals = CTE(
    rootmap.join(Order, region_id=rootmap.col.name)
    .values(root=rootmap.col.root)
    .annotate(
        orders_count=Count("id"),
        region_total=Sum("amount"),
    ),
    name="totals",
)

# Attach BOTH to the query
root_regions = with_cte(
    rootmap,
    totals,
    select=totals.join(Region, name=totals.col.root)
    .annotate(
        orders_count=totals.col.orders_count,
        region_total=totals.col.region_total,
    )
)

Recursive CTEs allow for hierarchical queries (like traversing tree structures). They are constructed using CTE.recursive(make_recursive_query_func).

The function passed to CTE.recursive must return a query that combines two elements using .union():

1. A non-recursive query element (the base case/root nodes).
2. A recursive query element (the step that joins the CTE back to the model to find descendants).

Use all=True in the .union() call to perform a UNION ALL.

def make_regions_cte(cte):
    # non-recursive: get root nodes
    return Region.objects.filter(
        parent__isnull=True
    ).values(
        "name",
        path=F("name"),
        depth=Value(0, output_field=IntegerField()),
    ).union(
        # recursive union: get descendants
        cte.join(Region, parent=cte.col.name).values(
            "name",
            path=Concat(
                cte.col.path, Value(" / "), F("name"),
                output_field=TextField(),
            ),
            depth=cte.col.depth + Value(1, output_field=IntegerField()),
        ),
        all=True,
    )

cte = CTE.recursive(make_regions_cte)

regions = with_cte(
    cte,
    select=cte.join(Region, name=cte.col.name)
    .annotate(
        path=cte.col.path,
        depth=cte.col.depth,
    )
    .filter(depth=2)
    .order_by("path")
)

Simple CTEs are temporary tables or views that exist only for the duration of a query. You can construct them using CTE(...) and attach them to a Django queryset using with_cte(cte, select=queryset). To link the CTE to your main query, use the <CTE>.join(...) method to create a JOIN and ON condition. You can then access CTE columns via <CTE>.col.<column_name> to annotate your queryset.

Note: django-cte always uses the WITH RECURSIVE keyword in the generated SQL, even for non-recursive CTEs. On databases like PostgreSQL and SQLite, this has no effect on non-recursive queries.

from django_cte import CTE, with_cte

# 1. Define the CTE
cte = CTE(
    Order.objects
    .values("region_id")
    .annotate(total=Sum("amount"))
)

# 2. Attach to queryset and join
orders = with_cte(
    cte,
    select=cte.join(Order, region=cte.col.region_id)
    .annotate(region_total=cte.col.total)
)

The CTEQuery class is a mixin for Django's Query objects that enables the use of Common Table Expressions (CTEs). It works by intercepting the SQL compilation process via a CTECompiler. When a query containing CTEs is compiled, CTEQuery collects all associated CTEs, generates their individual SQL statements, and prepends them to the main query using a WITH RECURSIVE clause.

Key behaviors:

• CTE Collection: It manages a collection of CTEs via the _with_ctes attribute.
• SQL Generation: It uses generate_cte_sql to wrap the base query with the WITH RECURSIVE syntax.
• Materialization: It respects the materialized attribute on CTEs, using the AS MATERIALIZED syntax when present.
• Recursive Support: The library always uses WITH RECURSIVE to ensure compatibility with recursive CTE requirements in PostgreSQL.

# Conceptual usage of a CTEQuery-enabled object
# (Note: Actual instantiation depends on the specific CTE implementation used)
query = some_cte_query_object
sql, params = query.get_compiler().as_sql()