{"id":5765,"library":"django-cte","title":"Common Table Expressions (CTE) for Django","description":"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.","status":"active","version":"3.0.0","language":"en","source_language":"en","source_url":"https://github.com/dimagi/django-cte","tags":["django","orm","cte","sql","database","query-optimization"],"install":[{"cmd":"pip install django-cte","lang":"bash","label":"Install stable version"}],"dependencies":[{"reason":"Core functionality as an ORM extension for Django. Requires a compatible Django version (not strictly version-locked in PyPI, but functional dependency).","package":"Django"}],"imports":[{"symbol":"CTE","correct":"from django_cte import CTE"},{"symbol":"with_cte","correct":"from django_cte import with_cte"},{"note":"Used for defining recursive CTEs.","symbol":"With","correct":"from django_cte import With"},{"note":"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.","wrong":"class MyModelManager(CTEManager):\n    pass\n\nclass MyModel(models.Model):\n    objects = MyModelManager()","symbol":"CustomModelManager"}],"quickstart":{"code":"import os\nfrom django.db import models\nfrom django.db.models import Sum\nfrom django_cte import CTE, with_cte\n\n# Assume a Django setup and an 'orders' app\n# For runnable example, we define a minimal model\n\nclass Order(models.Model):\n    region_id = models.IntegerField()\n    amount = models.DecimalField(max_digits=10, decimal_places=2)\n\n    class Meta:\n        app_label = 'orders'\n\n# Minimal Django settings for ORM to function\n# In a real project, this would be in settings.py\nif not os.environ.get('DJANGO_SETTINGS_MODULE'):\n    os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'myproject.settings')\n    try:\n        import django\n        django.setup()\n    except ImportError:\n        print(\"Django is not set up. This example requires a minimal Django environment.\")\n\n# Example: Calculate total amount per region using a CTE\n# and then join it back to annotate individual orders.\n\n# 1. Define the CTE\ncte_region_totals = CTE(\n    Order.objects\n    .values(\"region_id\")\n    .annotate(total_amount=Sum(\"amount\"))\n    .order_by()\n)\n\n# 2. Use with_cte to apply the CTE and join it to the main query\norders_with_region_totals = with_cte(\n    cte_region_totals,\n    select=cte_region_totals.join(Order, region_id=cte_region_totals.col.region_id)\n    .annotate(region_total_sum=cte_region_totals.col.total_amount)\n)\n\n# This would typically be executed in a view or script\n# For demonstration, we'll print the query.\n# In a real application, you would iterate orders_with_region_totals\n# For example:\n# for order in orders_with_region_totals.all():\n#     print(f\"Order {order.id} in region {order.region_id} has total for region: {order.region_total_sum}\")\n\nprint(\"Example CTE query (SQL):\")\nprint(str(orders_with_region_totals.all().query))\n\n# Example for recursive CTE (conceptual, requires more setup)\n# from django_cte import With\n# with_employees = With(\n#     Employee.objects.filter(manager__isnull=True).values('id', 'name', 'manager_id', 'depth', models.Value(0, output_field=models.IntegerField())),\n#     name='ancestors'\n# )\n# recursive_cte = with_employees.recursive(\n#     Employee.objects.filter(manager=with_employees.col.id)\n#     .values('id', 'name', 'manager_id', 'depth', with_employees.col.depth + 1)\n# )\n# employees_hierarchy = with_cte(recursive_cte, select=recursive_cte.join(Employee, id=recursive_cte.col.id))\n# print(str(employees_hierarchy.all().query))","lang":"python","description":"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."},"warnings":[{"fix":"Update your code to use the `CTE(...)` and `with_cte(...)` functions directly on standard Django QuerySets. Custom model managers are no longer needed.","message":"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.","severity":"breaking","affected_versions":"<2.0.0"},{"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.","message":"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.","severity":"gotcha","affected_versions":"All"},{"fix":"Ensure your database server meets the minimum version requirements if you intend to use materialized CTEs. Otherwise, omit the `materialized=True` parameter.","message":"The `materialized=True` option for CTEs is a database-specific feature. It is only supported by PostgreSQL 12+ and SQLite 3.35+.","severity":"gotcha","affected_versions":"All"}],"env_vars":null,"last_verified":"2026-04-14T00:00:00.000Z","next_check":"2026-07-13T00:00:00.000Z"}