# Common Table Expressions with Django

* Table of contents (this line will not be displayed).
{:toc}

A Common Table Expression acts like a temporary table or view that exists only
for the duration of the query it is attached to. django-cte allows common table
expressions to be attached to normal Django ORM queries.


## Simple Common Table Expressions

See [Appendix A](#appendix-a-model-definitions-used-in-sample-code) for model
definitions used in sample code.

Simple CTEs are constructed using `CTE(...)`. A CTE is added to a queryset using
`with_cte(cte, select=queryset)`, which adds the `WITH` expression before the
main `SELECT` query. A CTE can be joined to a model or other `QuerySet` using
its `<CTE>.join(...)` method, which creates a new queryset with a `JOIN` and
`ON` condition.

```py
from django_cte import CTE, with_cte

cte = CTE(
    Order.objects
    .values("region_id")
    .annotate(total=Sum("amount"))
)

orders = with_cte(
    # WITH cte ...
    cte,

    # SELECT ... FROM orders INNER JOIN cte ON orders.region_id = cte.region_id
    select=cte.join(Order, region=cte.col.region_id)

    # Annotate each Order with a "region_total"
    .annotate(region_total=cte.col.total)
)

print(orders.query)  # print SQL
```

The `orders` SQL, after formatting for readability, would look something like
this:

```sql
WITH RECURSIVE "cte" AS (
    SELECT
        "orders"."region_id",
        SUM("orders"."amount") AS "total"
    FROM "orders"
    GROUP BY "orders"."region_id"
)
SELECT
    "orders"."id",
    "orders"."region_id",
    "orders"."amount",
    "cte"."total" AS "region_total"
FROM "orders"
INNER JOIN "cte" ON "orders"."region_id" = "cte"."region_id"
```

The `orders` query is a queryset containing annotated `Order` objects, just as
you would get from a query like `Order.objects.annotate(region_total=...)`. Each
`Order` object will be annotated with a `region_total` attribute, which is
populated with the value of the corresponding total from the joined CTE query.

You may have noticed the CTE in this query uses `WITH RECURSIVE` even though
this is not a [Recursive Common Table Expression](#recursive-common-table-expressions).
The `RECURSIVE` keyword is always used, even for non-recursive CTEs. On
databases such as PostgreSQL and SQLite this has no effect other than allowing
recursive CTEs to be included in the WITH block.


## Recursive Common Table Expressions

Recursive CTE queries allow fundamentally new types of queries that are
not otherwise possible.

Recursive CTEs are constructed using `CTE.recursive()`, which takes as its
first argument a function that constructs and returns a recursive query.
Recursive queries have two elements: first a non-recursive query element, and
second a recursive query element. The second is typically attached to the first
using `QuerySet.union()`.

```py
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")
)
```

`Region` objects returned by this query will have `path` and `depth` attributes.
The results will be ordered by `path` (hierarchically by region name). The SQL
produced by this query looks something like this:

```sql
WITH RECURSIVE "cte" AS (
    SELECT
        "region"."name",
        "region"."name" AS "path",
        0 AS "depth"
    FROM "region"
    WHERE "region"."parent_id" IS NULL

    UNION ALL

    SELECT
        "region"."name",
        "cte"."path" || ' / ' || "region"."name" AS "path",
        "cte"."depth" + 1 AS "depth"
    FROM "region"
    INNER JOIN "cte" ON "region"."parent_id" = "cte"."name"
)
SELECT
    "region"."name",
    "region"."parent_id",
    "cte"."path" AS "path",
    "cte"."depth" AS "depth"
FROM "region"
INNER JOIN "cte" ON "region"."name" = "cte"."name"
WHERE "cte"."depth" = 2
ORDER BY "path" ASC
```


## Named Common Table Expressions

It is possible to add more than one CTE to a query. To do this, each CTE must
have a unique name. `CTE(queryset)` returns a CTE with the name `'cte'` by
default, but that can be overridden: `CTE(queryset, name='custom')` or
`CTE.recursive(make_queryset, name='custom')`. This allows each CTE to be
referenced uniquely within a single query.

Also note that a CTE may reference other CTEs in the same query.

Example query with two CTEs, and the second (`totals`) CTE references the first
(`rootmap`):

```py
def make_root_mapping(rootmap):
    return Region.objects.filter(
        parent__isnull=True
    ).values(
        "name",
        root=F("name"),
    ).union(
        rootmap.join(Region, parent=rootmap.col.name).values(
            "name",
            root=rootmap.col.root,
        ),
        all=True,
    )
rootmap = CTE.recursive(make_root_mapping, name="rootmap")

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",
)

root_regions = with_cte(
    # Important: add both CTEs to the query
    rootmap,
    totals,

    select=totals.join(Region, name=totals.col.root)
    .annotate(
        # count of orders in this region and all subregions
        orders_count=totals.col.orders_count,
        # sum of order amounts in this region and all subregions
        region_total=totals.col.region_total,
    )
)
```

And the resulting SQL.

```sql
WITH RECURSIVE "rootmap" AS (
    SELECT
        "region"."name",
        "region"."name" AS "root"
    FROM "region"
    WHERE "region"."parent_id" IS NULL

    UNION ALL

    SELECT
        "region"."name",
        "rootmap"."root" AS "root"
    FROM "region"
    INNER JOIN "rootmap" ON "region"."parent_id" = "rootmap"."name"
),
"totals" AS (
    SELECT
        "rootmap"."root" AS "root",
        COUNT("orders"."id") AS "orders_count",
        SUM("orders"."amount") AS "region_total"
    FROM "orders"
    INNER JOIN "rootmap" ON "orders"."region_id" = "rootmap"."name"
    GROUP BY "rootmap"."root"
)
SELECT
    "region"."name",
    "region"."parent_id",
    "totals"."orders_count" AS "orders_count",
    "totals"."region_total" AS "region_total"
FROM "region"
INNER JOIN "totals" ON "region"."name" = "totals"."root"
```


## Selecting FROM a Common Table Expression

Sometimes it is useful to construct queries where the final `FROM` clause
contains only common table expression(s). This is possible with
`CTE(...).queryset()`.

Each returned row may be a model object:

```py
cte = CTE(
    Order.objects
    .annotate(region_parent=F("region__parent_id")),
)
orders = with_cte(cte, select=cte.queryset())
```

And the resulting SQL:

```sql
WITH RECURSIVE "cte" AS (
    SELECT
        "orders"."id",
        "orders"."region_id",
        "orders"."amount",
        "region"."parent_id" AS "region_parent"
    FROM "orders"
    INNER JOIN "region" ON "orders"."region_id" = "region"."name"
)
SELECT
    "cte"."id",
    "cte"."region_id",
    "cte"."amount",
    "cte"."region_parent" AS "region_parent"
FROM "cte"
```

It is also possible to do the same with `values(...)` queries:

```py
cte = CTE(
    Order.objects
    .values(
        "region_id",
        region_parent=F("region__parent_id"),
    )
    .distinct()
)
values = with_cte(cte, select=cte).filter(region_parent__isnull=False)
```

Which produces this SQL:

```sql
WITH RECURSIVE "cte" AS (
    SELECT DISTINCT
        "orders"."region_id",
        "region"."parent_id" AS "region_parent"
    FROM "orders"
    INNER JOIN "region" ON "orders"."region_id" = "region"."name"
)
SELECT
    "cte"."region_id",
    "cte"."region_parent" AS "region_parent"
FROM "cte"
WHERE "cte"."region_parent" IS NOT NULL
```

You may have noticed that when a CTE is passed to the `select=...` argument as
in `with_cte(cte, select=cte)`, the `.queryset()` call is optional and may be
omitted.


## Experimental: Left Outer Join

Django does not provide precise control over joins, but there is an experimental
way to perform a `LEFT OUTER JOIN` with a CTE query using the `_join_type`
keyword argument of `CTE.join(...)`.

```py
from django.db.models.sql.constants import LOUTER

totals = CTE(
    Order.objects
    .values("region_id")
    .annotate(total=Sum("amount"))
    .filter(total__gt=100)
)
orders = with_cte(
    totals,
    select=totals
    .join(Order, region=totals.col.region_id, _join_type=LOUTER)
    .annotate(region_total=totals.col.total)
)
```

Which produces the following SQL

```sql
WITH RECURSIVE "cte" AS (
    SELECT
        "orders"."region_id",
        SUM("orders"."amount") AS "total"
    FROM "orders"
    GROUP BY "orders"."region_id"
    HAVING SUM("orders"."amount") > 100
)
SELECT
    "orders"."id",
    "orders"."region_id",
    "orders"."amount",
    "cte"."total" AS "region_total"
FROM "orders"
LEFT OUTER JOIN "cte" ON "orders"."region_id" = "cte"."region_id"
```

WARNING: as noted, this feature is experimental. There may be scenarios where
Django automatically converts a `LEFT OUTER JOIN` to an `INNER JOIN` in the
process of building the query. Be sure to test your queries to ensure they
produce the desired SQL.


## Materialized CTE

Both PostgreSQL 12+ and sqlite 3.35+ supports `MATERIALIZED` keyword for CTE
queries. To enforce usage of this keyword add `materialized` as a parameter of
`CTE(..., materialized=True)`.


```py
cte = CTE(
    Order.objects.values('id'),
    materialized=True
)
```

Which produces this SQL:

```sql
WITH RECURSIVE "cte" AS MATERIALIZED (
    SELECT 
        "orders"."id"
    FROM "orders"
)
...
```


## Raw CTE SQL

Some queries are easier to construct with raw SQL than with the Django ORM.
`raw_cte_sql()` is one solution for situations like that. The down-side is that
each result field in the raw query must be explicitly mapped to a field type.
The up-side is that there is no need to compromise result-set expressiveness
with the likes of `Manager.raw()`.

A short example:

```py
from django.db.models import IntegerField, TextField
from django_cte.raw import raw_cte_sql

cte = CTE(raw_cte_sql(
    """
    SELECT region_id, AVG(amount) AS avg_order
    FROM orders
    WHERE region_id = %s
    GROUP BY region_id
    """,
    ["moon"],
    {
        "region_id": TextField(),
        "avg_order": IntegerField(),
    },
))
moon_avg = with_cte(
    cte,
    select=cte
    .join(Region, name=cte.col.region_id)
    .annotate(avg_order=cte.col.avg_order)
)
```

Which produces this SQL:

```sql
WITH RECURSIVE "cte" AS (
    SELECT region_id, AVG(amount) AS avg_order
    FROM orders
    WHERE region_id = 'moon'
    GROUP BY region_id
)
SELECT
    "region"."name",
    "region"."parent_id",
    "cte"."avg_order" AS "avg_order"
FROM "region"
INNER JOIN "cte" ON "region"."name" = "cte"."region_id"
```

**WARNING**: Be very careful when writing raw SQL. Use bind parameters to
prevent SQL injection attacks.


## More Advanced Use Cases

A few more advanced techniques as well as example query results can be found
in the tests:

- [`test_cte.py`](https://github.com/dimagi/django-cte/blob/main/tests/test_cte.py)
- [`test_recursive.py`](https://github.com/dimagi/django-cte/blob/main/tests/test_recursive.py)
- [`test_raw.py`](https://github.com/dimagi/django-cte/blob/main/tests/test_raw.py)


## Appendix A: Model definitions used in sample code

```py
class Order(Model):
    id = AutoField(primary_key=True)
    region = ForeignKey("Region", on_delete=CASCADE)
    amount = IntegerField(default=0)

    class Meta:
        db_table = "orders"


class Region(Model):
    name = TextField(primary_key=True)
    parent = ForeignKey("self", null=True, on_delete=CASCADE)

    class Meta:
        db_table = "region"
```


## Appendix B: django-cte v1 documentation (DEPRECATED)

The syntax for constructing CTE queries changed slightly in django-cte 2.0. The
most important change is that a custom model manager is no longer required on
models used to construct CTE queries. The documentation has been updated to use
v2 syntax, but the [documentation for v1](https://github.com/dimagi/django-cte/blob/v1.3.3/docs/index.md)
can be found on Github if needed.