| 1 |
# Common Table Expressions with Django |
| 2 |
|
| 3 |
* Table of contents (this line will not be displayed). |
| 4 |
{:toc} |
| 5 |
|
| 6 |
A Common Table Expression acts like a temporary table or view that exists only |
| 7 |
for the duration of the query it is attached to. django-cte allows common table |
| 8 |
expressions to be attached to normal Django ORM queries. |
| 9 |
|
| 10 |
|
| 11 |
## Simple Common Table Expressions |
| 12 |
|
| 13 |
See [Appendix A](#appendix-a-model-definitions-used-in-sample-code) for model |
| 14 |
definitions used in sample code. |
| 15 |
|
| 16 |
Simple CTEs are constructed using `CTE(...)`. A CTE is added to a queryset using |
| 17 |
`with_cte(cte, select=queryset)`, which adds the `WITH` expression before the |
| 18 |
main `SELECT` query. A CTE can be joined to a model or other `QuerySet` using |
| 19 |
its `<CTE>.join(...)` method, which creates a new queryset with a `JOIN` and |
| 20 |
`ON` condition. |
| 21 |
|
| 22 |
```py |
| 23 |
from django_cte import CTE, with_cte |
| 24 |
|
| 25 |
cte = CTE( |
| 26 |
Order.objects |
| 27 |
.values("region_id") |
| 28 |
.annotate(total=Sum("amount")) |
| 29 |
) |
| 30 |
|
| 31 |
orders = with_cte( |
| 32 |
# WITH cte ... |
| 33 |
cte, |
| 34 |
|
| 35 |
# SELECT ... FROM orders INNER JOIN cte ON orders.region_id = cte.region_id |
| 36 |
select=cte.join(Order, region=cte.col.region_id) |
| 37 |
|
| 38 |
# Annotate each Order with a "region_total" |
| 39 |
.annotate(region_total=cte.col.total) |
| 40 |
) |
| 41 |
|
| 42 |
print(orders.query) # print SQL |
| 43 |
``` |
| 44 |
|
| 45 |
The `orders` SQL, after formatting for readability, would look something like |
| 46 |
this: |
| 47 |
|
| 48 |
```sql |
| 49 |
WITH RECURSIVE "cte" AS ( |
| 50 |
SELECT |
| 51 |
"orders"."region_id", |
| 52 |
SUM("orders"."amount") AS "total" |
| 53 |
FROM "orders" |
| 54 |
GROUP BY "orders"."region_id" |
| 55 |
) |
| 56 |
SELECT |
| 57 |
"orders"."id", |
| 58 |
"orders"."region_id", |
| 59 |
"orders"."amount", |
| 60 |
"cte"."total" AS "region_total" |
| 61 |
FROM "orders" |
| 62 |
INNER JOIN "cte" ON "orders"."region_id" = "cte"."region_id" |
| 63 |
``` |
| 64 |
|
| 65 |
The `orders` query is a queryset containing annotated `Order` objects, just as |
| 66 |
you would get from a query like `Order.objects.annotate(region_total=...)`. Each |
| 67 |
`Order` object will be annotated with a `region_total` attribute, which is |
| 68 |
populated with the value of the corresponding total from the joined CTE query. |
| 69 |
|
| 70 |
You may have noticed the CTE in this query uses `WITH RECURSIVE` even though |
| 71 |
this is not a [Recursive Common Table Expression](#recursive-common-table-expressions). |
| 72 |
The `RECURSIVE` keyword is always used, even for non-recursive CTEs. On |
| 73 |
databases such as PostgreSQL and SQLite this has no effect other than allowing |
| 74 |
recursive CTEs to be included in the WITH block. |
| 75 |
|
| 76 |
|
| 77 |
## Recursive Common Table Expressions |
| 78 |
|
| 79 |
Recursive CTE queries allow fundamentally new types of queries that are |
| 80 |
not otherwise possible. |
| 81 |
|
| 82 |
Recursive CTEs are constructed using `CTE.recursive()`, which takes as its |
| 83 |
first argument a function that constructs and returns a recursive query. |
| 84 |
Recursive queries have two elements: first a non-recursive query element, and |
| 85 |
second a recursive query element. The second is typically attached to the first |
| 86 |
using `QuerySet.union()`. |
| 87 |
|
| 88 |
```py |
| 89 |
def make_regions_cte(cte): |
| 90 |
# non-recursive: get root nodes |
| 91 |
return Region.objects.filter( |
| 92 |
parent__isnull=True |
| 93 |
).values( |
| 94 |
"name", |
| 95 |
path=F("name"), |
| 96 |
depth=Value(0, output_field=IntegerField()), |
| 97 |
).union( |
| 98 |
# recursive union: get descendants |
| 99 |
cte.join(Region, parent=cte.col.name).values( |
| 100 |
"name", |
| 101 |
path=Concat( |
| 102 |
cte.col.path, Value(" / "), F("name"), |
| 103 |
output_field=TextField(), |
| 104 |
), |
| 105 |
depth=cte.col.depth + Value(1, output_field=IntegerField()), |
| 106 |
), |
| 107 |
all=True, |
| 108 |
) |
| 109 |
|
| 110 |
cte = CTE.recursive(make_regions_cte) |
| 111 |
|
| 112 |
regions = with_cte( |
| 113 |
cte, |
| 114 |
select=cte.join(Region, name=cte.col.name) |
| 115 |
.annotate( |
| 116 |
path=cte.col.path, |
| 117 |
depth=cte.col.depth, |
| 118 |
) |
| 119 |
.filter(depth=2) |
| 120 |
.order_by("path") |
| 121 |
) |
| 122 |
``` |
| 123 |
|
| 124 |
`Region` objects returned by this query will have `path` and `depth` attributes. |
| 125 |
The results will be ordered by `path` (hierarchically by region name). The SQL |
| 126 |
produced by this query looks something like this: |
| 127 |
|
| 128 |
```sql |
| 129 |
WITH RECURSIVE "cte" AS ( |
| 130 |
SELECT |
| 131 |
"region"."name", |
| 132 |
"region"."name" AS "path", |
| 133 |
0 AS "depth" |
| 134 |
FROM "region" |
| 135 |
WHERE "region"."parent_id" IS NULL |
| 136 |
|
| 137 |
UNION ALL |
| 138 |
|
| 139 |
SELECT |
| 140 |
"region"."name", |
| 141 |
"cte"."path" || ' / ' || "region"."name" AS "path", |
| 142 |
"cte"."depth" + 1 AS "depth" |
| 143 |
FROM "region" |
| 144 |
INNER JOIN "cte" ON "region"."parent_id" = "cte"."name" |
| 145 |
) |
| 146 |
SELECT |
| 147 |
"region"."name", |
| 148 |
"region"."parent_id", |
| 149 |
"cte"."path" AS "path", |
| 150 |
"cte"."depth" AS "depth" |
| 151 |
FROM "region" |
| 152 |
INNER JOIN "cte" ON "region"."name" = "cte"."name" |
| 153 |
WHERE "cte"."depth" = 2 |
| 154 |
ORDER BY "path" ASC |
| 155 |
``` |
| 156 |
|
| 157 |
|
| 158 |
## Named Common Table Expressions |
| 159 |
|
| 160 |
It is possible to add more than one CTE to a query. To do this, each CTE must |
| 161 |
have a unique name. `CTE(queryset)` returns a CTE with the name `'cte'` by |
| 162 |
default, but that can be overridden: `CTE(queryset, name='custom')` or |
| 163 |
`CTE.recursive(make_queryset, name='custom')`. This allows each CTE to be |
| 164 |
referenced uniquely within a single query. |
| 165 |
|
| 166 |
Also note that a CTE may reference other CTEs in the same query. |
| 167 |
|
| 168 |
Example query with two CTEs, and the second (`totals`) CTE references the first |
| 169 |
(`rootmap`): |
| 170 |
|
| 171 |
```py |
| 172 |
def make_root_mapping(rootmap): |
| 173 |
return Region.objects.filter( |
| 174 |
parent__isnull=True |
| 175 |
).values( |
| 176 |
"name", |
| 177 |
root=F("name"), |
| 178 |
).union( |
| 179 |
rootmap.join(Region, parent=rootmap.col.name).values( |
| 180 |
"name", |
| 181 |
root=rootmap.col.root, |
| 182 |
), |
| 183 |
all=True, |
| 184 |
) |
| 185 |
rootmap = CTE.recursive(make_root_mapping, name="rootmap") |
| 186 |
|
| 187 |
totals = CTE( |
| 188 |
rootmap.join(Order, region_id=rootmap.col.name) |
| 189 |
.values( |
| 190 |
root=rootmap.col.root, |
| 191 |
).annotate( |
| 192 |
orders_count=Count("id"), |
| 193 |
region_total=Sum("amount"), |
| 194 |
), |
| 195 |
name="totals", |
| 196 |
) |
| 197 |
|
| 198 |
root_regions = with_cte( |
| 199 |
# Important: add both CTEs to the query |
| 200 |
rootmap, |
| 201 |
totals, |
| 202 |
|
| 203 |
select=totals.join(Region, name=totals.col.root) |
| 204 |
.annotate( |
| 205 |
# count of orders in this region and all subregions |
| 206 |
orders_count=totals.col.orders_count, |
| 207 |
# sum of order amounts in this region and all subregions |
| 208 |
region_total=totals.col.region_total, |
| 209 |
) |
| 210 |
) |
| 211 |
``` |
| 212 |
|
| 213 |
And the resulting SQL. |
| 214 |
|
| 215 |
```sql |
| 216 |
WITH RECURSIVE "rootmap" AS ( |
| 217 |
SELECT |
| 218 |
"region"."name", |
| 219 |
"region"."name" AS "root" |
| 220 |
FROM "region" |
| 221 |
WHERE "region"."parent_id" IS NULL |
| 222 |
|
| 223 |
UNION ALL |
| 224 |
|
| 225 |
SELECT |
| 226 |
"region"."name", |
| 227 |
"rootmap"."root" AS "root" |
| 228 |
FROM "region" |
| 229 |
INNER JOIN "rootmap" ON "region"."parent_id" = "rootmap"."name" |
| 230 |
), |
| 231 |
"totals" AS ( |
| 232 |
SELECT |
| 233 |
"rootmap"."root" AS "root", |
| 234 |
COUNT("orders"."id") AS "orders_count", |
| 235 |
SUM("orders"."amount") AS "region_total" |
| 236 |
FROM "orders" |
| 237 |
INNER JOIN "rootmap" ON "orders"."region_id" = "rootmap"."name" |
| 238 |
GROUP BY "rootmap"."root" |
| 239 |
) |
| 240 |
SELECT |
| 241 |
"region"."name", |
| 242 |
"region"."parent_id", |
| 243 |
"totals"."orders_count" AS "orders_count", |
| 244 |
"totals"."region_total" AS "region_total" |
| 245 |
FROM "region" |
| 246 |
INNER JOIN "totals" ON "region"."name" = "totals"."root" |
| 247 |
``` |
| 248 |
|
| 249 |
|
| 250 |
## Selecting FROM a Common Table Expression |
| 251 |
|
| 252 |
Sometimes it is useful to construct queries where the final `FROM` clause |
| 253 |
contains only common table expression(s). This is possible with |
| 254 |
`CTE(...).queryset()`. |
| 255 |
|
| 256 |
Each returned row may be a model object: |
| 257 |
|
| 258 |
```py |
| 259 |
cte = CTE( |
| 260 |
Order.objects |
| 261 |
.annotate(region_parent=F("region__parent_id")), |
| 262 |
) |
| 263 |
orders = with_cte(cte, select=cte.queryset()) |
| 264 |
``` |
| 265 |
|
| 266 |
And the resulting SQL: |
| 267 |
|
| 268 |
```sql |
| 269 |
WITH RECURSIVE "cte" AS ( |
| 270 |
SELECT |
| 271 |
"orders"."id", |
| 272 |
"orders"."region_id", |
| 273 |
"orders"."amount", |
| 274 |
"region"."parent_id" AS "region_parent" |
| 275 |
FROM "orders" |
| 276 |
INNER JOIN "region" ON "orders"."region_id" = "region"."name" |
| 277 |
) |
| 278 |
SELECT |
| 279 |
"cte"."id", |
| 280 |
"cte"."region_id", |
| 281 |
"cte"."amount", |
| 282 |
"cte"."region_parent" AS "region_parent" |
| 283 |
FROM "cte" |
| 284 |
``` |
| 285 |
|
| 286 |
It is also possible to do the same with `values(...)` queries: |
| 287 |
|
| 288 |
```py |
| 289 |
cte = CTE( |
| 290 |
Order.objects |
| 291 |
.values( |
| 292 |
"region_id", |
| 293 |
region_parent=F("region__parent_id"), |
| 294 |
) |
| 295 |
.distinct() |
| 296 |
) |
| 297 |
values = with_cte(cte, select=cte).filter(region_parent__isnull=False) |
| 298 |
``` |
| 299 |
|
| 300 |
Which produces this SQL: |
| 301 |
|
| 302 |
```sql |
| 303 |
WITH RECURSIVE "cte" AS ( |
| 304 |
SELECT DISTINCT |
| 305 |
"orders"."region_id", |
| 306 |
"region"."parent_id" AS "region_parent" |
| 307 |
FROM "orders" |
| 308 |
INNER JOIN "region" ON "orders"."region_id" = "region"."name" |
| 309 |
) |
| 310 |
SELECT |
| 311 |
"cte"."region_id", |
| 312 |
"cte"."region_parent" AS "region_parent" |
| 313 |
FROM "cte" |
| 314 |
WHERE "cte"."region_parent" IS NOT NULL |
| 315 |
``` |
| 316 |
|
| 317 |
You may have noticed that when a CTE is passed to the `select=...` argument as |
| 318 |
in `with_cte(cte, select=cte)`, the `.queryset()` call is optional and may be |
| 319 |
omitted. |
| 320 |
|
| 321 |
|
| 322 |
## Experimental: Left Outer Join |
| 323 |
|
| 324 |
Django does not provide precise control over joins, but there is an experimental |
| 325 |
way to perform a `LEFT OUTER JOIN` with a CTE query using the `_join_type` |
| 326 |
keyword argument of `CTE.join(...)`. |
| 327 |
|
| 328 |
```py |
| 329 |
from django.db.models.sql.constants import LOUTER |
| 330 |
|
| 331 |
totals = CTE( |
| 332 |
Order.objects |
| 333 |
.values("region_id") |
| 334 |
.annotate(total=Sum("amount")) |
| 335 |
.filter(total__gt=100) |
| 336 |
) |
| 337 |
orders = with_cte( |
| 338 |
totals, |
| 339 |
select=totals |
| 340 |
.join(Order, region=totals.col.region_id, _join_type=LOUTER) |
| 341 |
.annotate(region_total=totals.col.total) |
| 342 |
) |
| 343 |
``` |
| 344 |
|
| 345 |
Which produces the following SQL |
| 346 |
|
| 347 |
```sql |
| 348 |
WITH RECURSIVE "cte" AS ( |
| 349 |
SELECT |
| 350 |
"orders"."region_id", |
| 351 |
SUM("orders"."amount") AS "total" |
| 352 |
FROM "orders" |
| 353 |
GROUP BY "orders"."region_id" |
| 354 |
HAVING SUM("orders"."amount") > 100 |
| 355 |
) |
| 356 |
SELECT |
| 357 |
"orders"."id", |
| 358 |
"orders"."region_id", |
| 359 |
"orders"."amount", |
| 360 |
"cte"."total" AS "region_total" |
| 361 |
FROM "orders" |
| 362 |
LEFT OUTER JOIN "cte" ON "orders"."region_id" = "cte"."region_id" |
| 363 |
``` |
| 364 |
|
| 365 |
WARNING: as noted, this feature is experimental. There may be scenarios where |
| 366 |
Django automatically converts a `LEFT OUTER JOIN` to an `INNER JOIN` in the |
| 367 |
process of building the query. Be sure to test your queries to ensure they |
| 368 |
produce the desired SQL. |
| 369 |
|
| 370 |
|
| 371 |
## Materialized CTE |
| 372 |
|
| 373 |
Both PostgreSQL 12+ and sqlite 3.35+ supports `MATERIALIZED` keyword for CTE |
| 374 |
queries. To enforce usage of this keyword add `materialized` as a parameter of |
| 375 |
`CTE(..., materialized=True)`. |
| 376 |
|
| 377 |
|
| 378 |
```py |
| 379 |
cte = CTE( |
| 380 |
Order.objects.values('id'), |
| 381 |
materialized=True |
| 382 |
) |
| 383 |
``` |
| 384 |
|
| 385 |
Which produces this SQL: |
| 386 |
|
| 387 |
```sql |
| 388 |
WITH RECURSIVE "cte" AS MATERIALIZED ( |
| 389 |
SELECT |
| 390 |
"orders"."id" |
| 391 |
FROM "orders" |
| 392 |
) |
| 393 |
... |
| 394 |
``` |
| 395 |
|
| 396 |
|
| 397 |
## Raw CTE SQL |
| 398 |
|
| 399 |
Some queries are easier to construct with raw SQL than with the Django ORM. |
| 400 |
`raw_cte_sql()` is one solution for situations like that. The down-side is that |
| 401 |
each result field in the raw query must be explicitly mapped to a field type. |
| 402 |
The up-side is that there is no need to compromise result-set expressiveness |
| 403 |
with the likes of `Manager.raw()`. |
| 404 |
|
| 405 |
A short example: |
| 406 |
|
| 407 |
```py |
| 408 |
from django.db.models import IntegerField, TextField |
| 409 |
from django_cte.raw import raw_cte_sql |
| 410 |
|
| 411 |
cte = CTE(raw_cte_sql( |
| 412 |
""" |
| 413 |
SELECT region_id, AVG(amount) AS avg_order |
| 414 |
FROM orders |
| 415 |
WHERE region_id = %s |
| 416 |
GROUP BY region_id |
| 417 |
""", |
| 418 |
["moon"], |
| 419 |
{ |
| 420 |
"region_id": TextField(), |
| 421 |
"avg_order": IntegerField(), |
| 422 |
}, |
| 423 |
)) |
| 424 |
moon_avg = with_cte( |
| 425 |
cte, |
| 426 |
select=cte |
| 427 |
.join(Region, name=cte.col.region_id) |
| 428 |
.annotate(avg_order=cte.col.avg_order) |
| 429 |
) |
| 430 |
``` |
| 431 |
|
| 432 |
Which produces this SQL: |
| 433 |
|
| 434 |
```sql |
| 435 |
WITH RECURSIVE "cte" AS ( |
| 436 |
SELECT region_id, AVG(amount) AS avg_order |
| 437 |
FROM orders |
| 438 |
WHERE region_id = 'moon' |
| 439 |
GROUP BY region_id |
| 440 |
) |
| 441 |
SELECT |
| 442 |
"region"."name", |
| 443 |
"region"."parent_id", |
| 444 |
"cte"."avg_order" AS "avg_order" |
| 445 |
FROM "region" |
| 446 |
INNER JOIN "cte" ON "region"."name" = "cte"."region_id" |
| 447 |
``` |
| 448 |
|
| 449 |
**WARNING**: Be very careful when writing raw SQL. Use bind parameters to |
| 450 |
prevent SQL injection attacks. |
| 451 |
|
| 452 |
|
| 453 |
## More Advanced Use Cases |
| 454 |
|
| 455 |
A few more advanced techniques as well as example query results can be found |
| 456 |
in the tests: |
| 457 |
|
| 458 |
- [`test_cte.py`](https://github.com/dimagi/django-cte/blob/main/tests/test_cte.py) |
| 459 |
- [`test_recursive.py`](https://github.com/dimagi/django-cte/blob/main/tests/test_recursive.py) |
| 460 |
- [`test_raw.py`](https://github.com/dimagi/django-cte/blob/main/tests/test_raw.py) |
| 461 |
|
| 462 |
|
| 463 |
## Appendix A: Model definitions used in sample code |
| 464 |
|
| 465 |
```py |
| 466 |
class Order(Model): |
| 467 |
id = AutoField(primary_key=True) |
| 468 |
region = ForeignKey("Region", on_delete=CASCADE) |
| 469 |
amount = IntegerField(default=0) |
| 470 |
|
| 471 |
class Meta: |
| 472 |
db_table = "orders" |
| 473 |
|
| 474 |
|
| 475 |
class Region(Model): |
| 476 |
name = TextField(primary_key=True) |
| 477 |
parent = ForeignKey("self", null=True, on_delete=CASCADE) |
| 478 |
|
| 479 |
class Meta: |
| 480 |
db_table = "region" |
| 481 |
``` |
| 482 |
|
| 483 |
|
| 484 |
## Appendix B: django-cte v1 documentation (DEPRECATED) |
| 485 |
|
| 486 |
The syntax for constructing CTE queries changed slightly in django-cte 2.0. The |
| 487 |
most important change is that a custom model manager is no longer required on |
| 488 |
models used to construct CTE queries. The documentation has been updated to use |
| 489 |
v2 syntax, but the [documentation for v1](https://github.com/dimagi/django-cte/blob/v1.3.3/docs/index.md) |
| 490 |
can be found on Github if needed. |