Common Table Expressions (CTE) for Django

3.0.0 · active · verified Tue Apr 14

django-cte provides a seamless way to integrate Common Table Expressions (CTEs) into Django's ORM, allowing developers to write complex, hierarchical, and recursive SQL queries more readably and efficiently directly within their Django applications. It is currently at version 3.0.0 and is actively maintained, with releases primarily driven by new features or compatibility requirements with Django versions.

Warnings

breaking The API for defining CTEs changed significantly in `django-cte` v2.0. The requirement for a custom model manager on your models for CTE queries was removed.
Fix: Update your code to use the `CTE(...)` and `with_cte(...)` functions directly on standard Django QuerySets. Custom model managers are no longer needed.
gotcha While `django-cte` brings CTEs to Django's ORM, it's an abstraction. Developers should still have a foundational understanding of SQL CTEs to effectively use the library, especially for complex or recursive queries. The `with_cte` function modifies querysets in specific ways that might differ from native ORM chaining.
Fix: Familiarize yourself with SQL Common Table Expressions and review the `django-cte` documentation for patterns on simple, named, recursive, and materialized CTEs to ensure correct ORM integration.
gotcha The `materialized=True` option for CTEs is a database-specific feature. It is only supported by PostgreSQL 12+ and SQLite 3.35+.
Fix: Ensure your database server meets the minimum version requirements if you intend to use materialized CTEs. Otherwise, omit the `materialized=True` parameter.

Install

pip install django-cte Install stable version

Imports

CTE
```
from django_cte import CTE
```
with_cte
```
from django_cte import with_cte
```
With
```
from django_cte import With
```
Used for defining recursive CTEs.
CustomModelManager
Prior to v2.0, a custom model manager (e.g., inheriting from CTEManager or similar) was often required. This is no longer necessary in v2.0 and later.

Quickstart

This quickstart demonstrates how to define a simple CTE to calculate aggregated values (sum of amounts per region) and then join it back to the original `Order` model to annotate each order with its region's total. For recursive CTEs, the `With` class is used, followed by a `.recursive()` call.

import os
from django.db import models
from django.db.models import Sum
from django_cte import CTE, with_cte

# Assume a Django setup and an 'orders' app
# For runnable example, we define a minimal model

class Order(models.Model):
    region_id = models.IntegerField()
    amount = models.DecimalField(max_digits=10, decimal_places=2)

    class Meta:
        app_label = 'orders'

# Minimal Django settings for ORM to function
# In a real project, this would be in settings.py
if not os.environ.get('DJANGO_SETTINGS_MODULE'):
    os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'myproject.settings')
    try:
        import django
        django.setup()
    except ImportError:
        print("Django is not set up. This example requires a minimal Django environment.")

# Example: Calculate total amount per region using a CTE
# and then join it back to annotate individual orders.

# 1. Define the CTE
cte_region_totals = CTE(
    Order.objects
    .values("region_id")
    .annotate(total_amount=Sum("amount"))
    .order_by()
)

# 2. Use with_cte to apply the CTE and join it to the main query
orders_with_region_totals = with_cte(
    cte_region_totals,
    select=cte_region_totals.join(Order, region_id=cte_region_totals.col.region_id)
    .annotate(region_total_sum=cte_region_totals.col.total_amount)
)

# This would typically be executed in a view or script
# For demonstration, we'll print the query.
# In a real application, you would iterate orders_with_region_totals
# For example:
# for order in orders_with_region_totals.all():
#     print(f"Order {order.id} in region {order.region_id} has total for region: {order.region_total_sum}")

print("Example CTE query (SQL):")
print(str(orders_with_region_totals.all().query))

# Example for recursive CTE (conceptual, requires more setup)
# from django_cte import With
# with_employees = With(
#     Employee.objects.filter(manager__isnull=True).values('id', 'name', 'manager_id', 'depth', models.Value(0, output_field=models.IntegerField())),
#     name='ancestors'
# )
# recursive_cte = with_employees.recursive(
#     Employee.objects.filter(manager=with_employees.col.id)
#     .values('id', 'name', 'manager_id', 'depth', with_employees.col.depth + 1)
# )
# employees_hierarchy = with_cte(recursive_cte, select=recursive_cte.join(Employee, id=recursive_cte.col.id))
# print(str(employees_hierarchy.all().query))

view raw JSON →