Debian Codegraph @

docs/index.md

raw
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.