django-cte

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