### Raw CTE SQL (Python)

Source: https://dimagi.github.io/django-cte/index

Provides an example of using `raw_cte_sql()` for constructing CTEs with raw SQL, including mapping result fields and using bind parameters.

```python
from django.db.models import IntegerField, TextField
from django_cte.raw import raw_cte_sql
from django_cte import CTE, with_cte

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

--------------------------------

### Simple CTE with Django ORM

Source: https://dimagi.github.io/django-cte/index

Demonstrates how to create a simple Common Table Expression (CTE) using django-cte and join it with a Django ORM queryset. This example annotates Order objects with their respective region totals.

```python
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
```

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

--------------------------------

### Recursive CTE for Region Hierarchy

Source: https://dimagi.github.io/django-cte/index

Demonstrates creating a recursive CTE to fetch region names, their hierarchical paths, and depths. It uses CTE.recursive() and QuerySet.union() to build the recursive query, starting with root nodes and then joining to find descendants. The results are filtered by depth and ordered by path.

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

```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
```

--------------------------------

### Experimental Left Outer Join (SQL)

Source: https://dimagi.github.io/django-cte/index

Illustrates the SQL generated for a LEFT OUTER JOIN with a CTE, demonstrating the LEFT OUTER JOIN clause.

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

--------------------------------

### Raw CTE SQL (SQL)

Source: https://dimagi.github.io/django-cte/index

Illustrates the SQL generated from a raw CTE query, showing the embedded SQL and how it's joined with other tables.

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

--------------------------------

### Experimental Left Outer Join (Python)

Source: https://dimagi.github.io/django-cte/index

Shows how to perform a LEFT OUTER JOIN with a CTE query using the `_join_type` keyword argument of `CTE.join()`. This feature is experimental.

```python
from django.db.models.sql.constants import LOUTER
from django.db.models import Sum
from django_cte import CTE, with_cte

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

--------------------------------

### Materialized CTE (SQL)

Source: https://dimagi.github.io/django-cte/index

Shows the SQL output when using a materialized CTE, including the `AS MATERIALIZED` clause.

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

--------------------------------

### Selecting FROM a Common Table Expression (Python)

Source: https://dimagi.github.io/django-cte/index

Demonstrates how to construct queries where the final FROM clause contains only common table expressions using CTE(...).queryset(). Supports both model objects and values() queries.

```python
from django.db.models import F
from django_cte import CTE, with_cte

# Example with model objects
cte = CTE(
    Order.objects
    .annotate(region_parent=F("region__parent_id")),
)
orders = with_cte(cte, select=cte.queryset())

# Example with values() queries
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)
```

--------------------------------

### Materialized CTE (Python)

Source: https://dimagi.github.io/django-cte/index

Demonstrates how to enforce the use of the `MATERIALIZED` keyword for CTE queries by setting `materialized=True` in the `CTE` constructor.

```python
from django_cte import CTE

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

--------------------------------

### Selecting FROM a Common Table Expression (SQL)

Source: https://dimagi.github.io/django-cte/index

Illustrates the SQL generated when selecting from a CTE, showing the WITH RECURSIVE clause and the subsequent SELECT statement.

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

```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
```

--------------------------------

### Named CTEs for Region Mapping and Order Totals

Source: https://dimagi.github.io/django-cte/index

Illustrates using multiple named CTEs in a single query. The 'rootmap' CTE recursively maps regions to their root ancestors, and the 'totals' CTE calculates order counts and sums for each root region by joining with the 'rootmap' CTE. The final query joins the 'totals' CTE with the 'Region' table to annotate regions with aggregated order data.

```python
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,
    )
)
```

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

--------------------------------

### Django Model Definitions for CTE

Source: https://dimagi.github.io/django-cte/index

Defines Django models 'Order' and 'Region' used in sample code for Common Table Expressions (CTE) with django-cte. Includes fields like ForeignKey, AutoField, IntegerField, and TextField.

```python
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"
```

=== COMPLETE CONTENT === This response contains all available snippets from this library. No additional content exists. Do not make further requests.