Debian Codegraph @

docs/ref/models/expressions.txt

raw
1 =================
2 Query Expressions
3 =================
4
5 .. currentmodule:: django.db.models
6
7 Query expressions describe a value or a computation that can be used as part of
8 an update, create, filter, order by, annotation, or aggregate. When an
9 expression outputs a boolean value, it may be used directly in filters. There
10 are a number of built-in expressions (documented below) that can be used to
11 help you write queries. Expressions can be combined, or in some cases nested,
12 to form more complex computations.
13
14 Supported arithmetic
15 ====================
16
17 Django supports negation, addition, subtraction, multiplication, division,
18 modulo arithmetic, and the power operator on query expressions, using Python
19 constants, variables, and even other expressions.
20
21 .. _output-field:
22
23 Output field
24 ============
25
26 Many of the expressions documented in this section support an optional
27 ``output_field`` parameter. If given, Django will load the value into that
28 field after retrieving it from the database.
29
30 ``output_field`` takes a model field instance, like ``IntegerField()`` or
31 ``BooleanField()``. Usually, the field doesn't need any arguments, like
32 ``max_length``, since field arguments relate to data validation which will not
33 be performed on the expression's output value.
34
35 ``output_field`` is only required when Django is unable to automatically
36 determine the result's field type, such as complex expressions that mix field
37 types. For example, adding a ``DecimalField()`` and a ``FloatField()`` requires
38 an output field, like ``output_field=FloatField()``.
39
40 ``output_field`` also allows using custom fields that perform type conversions
41 outside a specific model field context. For example, if you frequently need to
42 perform date arithmetic with ``timedelta``, you can create a custom field that
43 handles the conversion, ensuring consistent results across databases. See
44 :doc:`/howto/custom-model-fields`.
45
46 Some examples
47 =============
48
49 .. code-block:: pycon
50
51 >>> from django.db.models import Count, F, Value
52 >>> from django.db.models.functions import Length, Upper
53 >>> from django.db.models.lookups import GreaterThan
54
55 # Find companies that have more employees than chairs.
56 >>> Company.objects.filter(num_employees__gt=F("num_chairs"))
57
58 # Find companies that have at least twice as many employees
59 # as chairs. Both the querysets below are equivalent.
60 >>> Company.objects.filter(num_employees__gt=F("num_chairs") * 2)
61 >>> Company.objects.filter(num_employees__gt=F("num_chairs") + F("num_chairs"))
62
63 # How many chairs are needed for each company to seat all employees?
64 >>> company = (
65 ... Company.objects.filter(num_employees__gt=F("num_chairs"))
66 ... .annotate(chairs_needed=F("num_employees") - F("num_chairs"))
67 ... .first()
68 ... )
69 >>> company.num_employees
70 120
71 >>> company.num_chairs
72 50
73 >>> company.chairs_needed
74 70
75
76 # Create a new company using expressions.
77 >>> company = Company.objects.create(name="Google", ticker=Upper(Value("goog")))
78 # Be sure to refresh it if you need to access the field.
79 >>> company.refresh_from_db()
80 >>> company.ticker
81 'GOOG'
82
83 # Annotate models with an aggregated value. Both forms
84 # below are equivalent.
85 >>> Company.objects.annotate(num_products=Count("products"))
86 >>> Company.objects.annotate(num_products=Count(F("products")))
87
88 # Aggregates can contain complex computations also
89 >>> Company.objects.annotate(num_offerings=Count(F("products") + F("services")))
90
91 # Expressions can also be used in order_by(), either directly
92 >>> Company.objects.order_by(Length("name").asc())
93 >>> Company.objects.order_by(Length("name").desc())
94 # or using the double underscore lookup syntax.
95 >>> from django.db.models import CharField
96 >>> from django.db.models.functions import Length
97 >>> CharField.register_lookup(Length)
98 >>> Company.objects.order_by("name__length")
99
100 # Boolean expression can be used directly in filters.
101 >>> from django.db.models import Exists, OuterRef
102 >>> Company.objects.filter(
103 ... Exists(Employee.objects.filter(company=OuterRef("pk"), salary__gt=10))
104 ... )
105
106 # Lookup expressions can also be used directly in filters
107 >>> Company.objects.filter(GreaterThan(F("num_employees"), F("num_chairs")))
108 # or annotations.
109 >>> Company.objects.annotate(
110 ... need_chairs=GreaterThan(F("num_employees"), F("num_chairs")),
111 ... )
112
113 Built-in Expressions
114 ====================
115
116 .. note::
117
118 These expressions are defined in ``django.db.models.expressions`` and
119 ``django.db.models.aggregates``, but for convenience they're available and
120 usually imported from :mod:`django.db.models`.
121
122 ``F()`` expressions
123 -------------------
124
125 .. class:: F
126
127 An ``F()`` object represents the value of a model field, transformed value of a
128 model field, or annotated column. It makes it possible to refer to model field
129 values and perform database operations using them without actually having to
130 pull them out of the database into Python memory.
131
132 Instead, Django uses the ``F()`` object to generate an SQL expression that
133 describes the required operation at the database level.
134
135 Let's try this with an example. Normally, one might do something like this::
136
137 # Tintin filed a news story!
138 reporter = Reporters.objects.get(name="Tintin")
139 reporter.stories_filed += 1
140 reporter.save()
141
142 Here, we have pulled the value of ``reporter.stories_filed`` from the database
143 into memory and manipulated it using familiar Python operators, and then saved
144 the object back to the database. But instead we could also have done::
145
146 from django.db.models import F
147
148 reporter = Reporters.objects.get(name="Tintin")
149 reporter.stories_filed = F("stories_filed") + 1
150 reporter.save()
151
152 Although ``reporter.stories_filed = F('stories_filed') + 1`` looks like a
153 normal Python assignment of value to an instance attribute, in fact it's an SQL
154 construct describing an operation on the database.
155
156 When Django encounters an instance of ``F()``, it overrides the standard Python
157 operators to create an encapsulated SQL expression; in this case, one which
158 instructs the database to increment the database field represented by
159 ``reporter.stories_filed``.
160
161 Whatever value is or was on ``reporter.stories_filed``, Python never gets to
162 know about it - it is dealt with entirely by the database. All Python does,
163 through Django's ``F()`` class, is create the SQL syntax to refer to the field
164 and describe the operation.
165
166 To access the new value saved this way, the object must be reloaded::
167
168 reporter = Reporters.objects.get(pk=reporter.pk)
169 # Or, more succinctly:
170 reporter.refresh_from_db()
171
172 As well as being used in operations on single instances as above, ``F()`` can
173 be used with ``update()`` to perform bulk updates on a ``QuerySet``. This
174 reduces the two queries we were using above - the ``get()`` and the
175 :meth:`~Model.save()` - to just one::
176
177 reporter = Reporters.objects.filter(name="Tintin")
178 reporter.update(stories_filed=F("stories_filed") + 1)
179
180 We can also use :meth:`~django.db.models.query.QuerySet.update()` to increment
181 the field value on multiple objects - which could be very much faster than
182 pulling them all into Python from the database, looping over them, incrementing
183 the field value of each one, and saving each one back to the database::
184
185 Reporter.objects.update(stories_filed=F("stories_filed") + 1)
186
187 ``F()`` therefore can offer performance advantages by:
188
189 * getting the database, rather than Python, to do work
190 * reducing the number of queries some operations require
191
192 .. _slicing-using-f:
193
194 Slicing ``F()`` expressions
195 ~~~~~~~~~~~~~~~~~~~~~~~~~~~
196
197 .. versionadded:: 5.1
198
199 For string-based fields, text-based fields, and
200 :class:`~django.contrib.postgres.fields.ArrayField`, you can use Python's
201 array-slicing syntax. The indices are 0-based. The ``step`` argument to
202 ``slice`` and negative indexing are not supported. For example:
203
204 .. code-block:: pycon
205
206 >>> # Replacing a name with a substring of itself.
207 >>> writer = Writers.objects.get(name="Priyansh")
208 >>> writer.name = F("name")[1:5]
209 >>> writer.save()
210 >>> writer.refresh_from_db()
211 >>> writer.name
212 'riya'
213
214 .. _avoiding-race-conditions-using-f:
215
216 Avoiding race conditions using ``F()``
217 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
218
219 Another useful benefit of ``F()`` is that having the database - rather than
220 Python - update a field's value avoids a *race condition*.
221
222 If two Python threads execute the code in the first example above, one thread
223 could retrieve, increment, and save a field's value after the other has
224 retrieved it from the database. The value that the second thread saves will be
225 based on the original value; the work of the first thread will be lost.
226
227 If the database is responsible for updating the field, the process is more
228 robust: it will only ever update the field based on the value of the field in
229 the database when the :meth:`~Model.save()` or ``update()`` is executed, rather
230 than based on its value when the instance was retrieved.
231
232 ``F()`` assignments persist after ``Model.save()``
233 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
234
235 ``F()`` objects assigned to model fields persist after saving the model
236 instance and will be applied on each :meth:`~Model.save()`. For example::
237
238 reporter = Reporters.objects.get(name="Tintin")
239 reporter.stories_filed = F("stories_filed") + 1
240 reporter.save()
241
242 reporter.name = "Tintin Jr."
243 reporter.save()
244
245 ``stories_filed`` will be updated twice in this case. If it's initially ``1``,
246 the final value will be ``3``. This persistence can be avoided by reloading the
247 model object after saving it, for example, by using
248 :meth:`~Model.refresh_from_db()`.
249
250 Using ``F()`` in filters
251 ~~~~~~~~~~~~~~~~~~~~~~~~
252
253 ``F()`` is also very useful in ``QuerySet`` filters, where they make it
254 possible to filter a set of objects against criteria based on their field
255 values, rather than on Python values.
256
257 This is documented in :ref:`using F() expressions in queries
258 <using-f-expressions-in-filters>`.
259
260 .. _using-f-with-annotations:
261
262 Using ``F()`` with annotations
263 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
264
265 ``F()`` can be used to create dynamic fields on your models by combining
266 different fields with arithmetic::
267
268 company = Company.objects.annotate(chairs_needed=F("num_employees") - F("num_chairs"))
269
270 If the fields that you're combining are of different types you'll need to tell
271 Django what kind of field will be returned. Most expressions support
272 :ref:`output_field<output-field>` for this case, but since ``F()`` does not, you
273 will need to wrap the expression with :class:`ExpressionWrapper`::
274
275 from django.db.models import DateTimeField, ExpressionWrapper, F
276
277 Ticket.objects.annotate(
278 expires=ExpressionWrapper(
279 F("active_at") + F("duration"), output_field=DateTimeField()
280 )
281 )
282
283 When referencing relational fields such as ``ForeignKey``, ``F()`` returns the
284 primary key value rather than a model instance:
285
286 .. code-block:: pycon
287
288 >>> car = Company.objects.annotate(built_by=F("manufacturer"))[0]
289 >>> car.manufacturer
290 <Manufacturer: Toyota>
291 >>> car.built_by
292 3
293
294 .. _using-f-to-sort-null-values:
295
296 Using ``F()`` to sort null values
297 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
298
299 Use ``F()`` and the ``nulls_first`` or ``nulls_last`` keyword argument to
300 :meth:`.Expression.asc` or :meth:`~.Expression.desc` to control the ordering of
301 a field's null values. By default, the ordering depends on your database.
302
303 For example, to sort companies that haven't been contacted (``last_contacted``
304 is null) after companies that have been contacted::
305
306 from django.db.models import F
307
308 Company.objects.order_by(F("last_contacted").desc(nulls_last=True))
309
310 Using ``F()`` with logical operations
311 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
312
313 ``F()`` expressions that output ``BooleanField`` can be logically negated with
314 the inversion operator ``~F()``. For example, to swap the activation status of
315 companies::
316
317 from django.db.models import F
318
319 Company.objects.update(is_active=~F("is_active"))
320
321 .. _func-expressions:
322
323 ``Func()`` expressions
324 ----------------------
325
326 ``Func()`` expressions are the base type of all expressions that involve
327 database functions like ``COALESCE`` and ``LOWER``, or aggregates like ``SUM``.
328 They can be used directly::
329
330 from django.db.models import F, Func
331
332 queryset.annotate(field_lower=Func(F("field"), function="LOWER"))
333
334 or they can be used to build a library of database functions::
335
336 class Lower(Func):
337 function = "LOWER"
338
339
340 queryset.annotate(field_lower=Lower("field"))
341
342 But both cases will result in a queryset where each model is annotated with an
343 extra attribute ``field_lower`` produced, roughly, from the following SQL:
344
345 .. code-block:: sql
346
347 SELECT
348 ...
349 LOWER("db_table"."field") as "field_lower"
350
351 See :doc:`database-functions` for a list of built-in database functions.
352
353 The ``Func`` API is as follows:
354
355 .. class:: Func(*expressions, **extra)
356
357 .. attribute:: function
358
359 A class attribute describing the function that will be generated.
360 Specifically, the ``function`` will be interpolated as the ``function``
361 placeholder within :attr:`template`. Defaults to ``None``.
362
363 .. attribute:: template
364
365 A class attribute, as a format string, that describes the SQL that is
366 generated for this function. Defaults to
367 ``'%(function)s(%(expressions)s)'``.
368
369 If you're constructing SQL like ``strftime('%W', 'date')`` and need a
370 literal ``%`` character in the query, quadruple it (``%%%%``) in the
371 ``template`` attribute because the string is interpolated twice: once
372 during the template interpolation in ``as_sql()`` and once in the SQL
373 interpolation with the query parameters in the database cursor.
374
375 .. attribute:: arg_joiner
376
377 A class attribute that denotes the character used to join the list of
378 ``expressions`` together. Defaults to ``', '``.
379
380 .. attribute:: arity
381
382 A class attribute that denotes the number of arguments the function
383 accepts. If this attribute is set and the function is called with a
384 different number of expressions, ``TypeError`` will be raised. Defaults
385 to ``None``.
386
387 .. method:: as_sql(compiler, connection, function=None, template=None, arg_joiner=None, **extra_context)
388
389 Generates the SQL fragment for the database function. Returns a tuple
390 ``(sql, params)``, where ``sql`` is the SQL string, and ``params`` is
391 the list or tuple of query parameters.
392
393 The ``as_vendor()`` methods should use the ``function``, ``template``,
394 ``arg_joiner``, and any other ``**extra_context`` parameters to
395 customize the SQL as needed. For example:
396
397 .. code-block:: python
398 :caption: ``django/db/models/functions.py``
399
400 class ConcatPair(Func):
401 ...
402 function = "CONCAT"
403 ...
404
405 def as_mysql(self, compiler, connection, **extra_context):
406 return super().as_sql(
407 compiler,
408 connection,
409 function="CONCAT_WS",
410 template="%(function)s('', %(expressions)s)",
411 **extra_context
412 )
413
414 To avoid an SQL injection vulnerability, ``extra_context`` :ref:`must
415 not contain untrusted user input <avoiding-sql-injection-in-query-expressions>`
416 as these values are interpolated into the SQL string rather than passed
417 as query parameters, where the database driver would escape them.
418
419 The ``*expressions`` argument is a list of positional expressions that the
420 function will be applied to. The expressions will be converted to strings,
421 joined together with ``arg_joiner``, and then interpolated into the ``template``
422 as the ``expressions`` placeholder.
423
424 Positional arguments can be expressions or Python values. Strings are
425 assumed to be column references and will be wrapped in ``F()`` expressions
426 while other values will be wrapped in ``Value()`` expressions.
427
428 The ``**extra`` kwargs are ``key=value`` pairs that can be interpolated
429 into the ``template`` attribute. To avoid an SQL injection vulnerability,
430 ``extra`` :ref:`must not contain untrusted user input
431 <avoiding-sql-injection-in-query-expressions>` as these values are interpolated
432 into the SQL string rather than passed as query parameters, where the database
433 driver would escape them.
434
435 The ``function``, ``template``, and ``arg_joiner`` keywords can be used to
436 replace the attributes of the same name without having to define your own
437 class. :ref:`output_field<output-field>` can be used to define the expected
438 return type.
439
440 ``Aggregate()`` expressions
441 ---------------------------
442
443 An aggregate expression is a special case of a :ref:`Func() expression
444 <func-expressions>` that informs the query that a ``GROUP BY`` clause
445 is required. All of the :ref:`aggregate functions <aggregation-functions>`,
446 like ``Sum()`` and ``Count()``, inherit from ``Aggregate()``.
447
448 Since ``Aggregate``\s are expressions and wrap expressions, you can represent
449 some complex computations::
450
451 from django.db.models import Count
452
453 Company.objects.annotate(
454 managers_required=(Count("num_employees") / 4) + Count("num_managers")
455 )
456
457 The ``Aggregate`` API is as follows:
458
459 .. class:: Aggregate(*expressions, output_field=None, distinct=False, filter=None, default=None, **extra)
460
461 .. attribute:: template
462
463 A class attribute, as a format string, that describes the SQL that is
464 generated for this aggregate. Defaults to
465 ``'%(function)s(%(distinct)s%(expressions)s)'``.
466
467 .. attribute:: function
468
469 A class attribute describing the aggregate function that will be
470 generated. Specifically, the ``function`` will be interpolated as the
471 ``function`` placeholder within :attr:`template`. Defaults to ``None``.
472
473 .. attribute:: window_compatible
474
475 Defaults to ``True`` since most aggregate functions can be used as the
476 source expression in :class:`~django.db.models.expressions.Window`.
477
478 .. attribute:: allow_distinct
479
480 A class attribute determining whether or not this aggregate function
481 allows passing a ``distinct`` keyword argument. If set to ``False``
482 (default), ``TypeError`` is raised if ``distinct=True`` is passed.
483
484 .. attribute:: empty_result_set_value
485
486 Defaults to ``None`` since most aggregate functions result in ``NULL``
487 when applied to an empty result set.
488
489 The ``expressions`` positional arguments can include expressions, transforms of
490 the model field, or the names of model fields. They will be converted to a
491 string and used as the ``expressions`` placeholder within the ``template``.
492
493 The ``distinct`` argument determines whether or not the aggregate function
494 should be invoked for each distinct value of ``expressions`` (or set of
495 values, for multiple ``expressions``). The argument is only supported on
496 aggregates that have :attr:`~Aggregate.allow_distinct` set to ``True``.
497
498 The ``filter`` argument takes a :class:`Q object <django.db.models.Q>` that's
499 used to filter the rows that are aggregated. See :ref:`conditional-aggregation`
500 and :ref:`filtering-on-annotations` for example usage.
501
502 The ``default`` argument takes a value that will be passed along with the
503 aggregate to :class:`~django.db.models.functions.Coalesce`. This is useful for
504 specifying a value to be returned other than ``None`` when the queryset (or
505 grouping) contains no entries.
506
507 The ``**extra`` kwargs are ``key=value`` pairs that can be interpolated
508 into the ``template`` attribute.
509
510 Creating your own Aggregate Functions
511 -------------------------------------
512
513 You can create your own aggregate functions, too. At a minimum, you need to
514 define ``function``, but you can also completely customize the SQL that is
515 generated. Here's a brief example::
516
517 from django.db.models import Aggregate
518
519
520 class Sum(Aggregate):
521 # Supports SUM(ALL field).
522 function = "SUM"
523 template = "%(function)s(%(all_values)s%(expressions)s)"
524 allow_distinct = False
525 arity = 1
526
527 def __init__(self, expression, all_values=False, **extra):
528 super().__init__(expression, all_values="ALL " if all_values else "", **extra)
529
530 ``Value()`` expressions
531 -----------------------
532
533 .. class:: Value(value, output_field=None)
534
535
536 A ``Value()`` object represents the smallest possible component of an
537 expression: a simple value. When you need to represent the value of an integer,
538 boolean, or string within an expression, you can wrap that value within a
539 ``Value()``.
540
541 You will rarely need to use ``Value()`` directly. When you write the expression
542 ``F('field') + 1``, Django implicitly wraps the ``1`` in a ``Value()``,
543 allowing simple values to be used in more complex expressions. You will need to
544 use ``Value()`` when you want to pass a string to an expression. Most
545 expressions interpret a string argument as the name of a field, like
546 ``Lower('name')``.
547
548 The ``value`` argument describes the value to be included in the expression,
549 such as ``1``, ``True``, or ``None``. Django knows how to convert these Python
550 values into their corresponding database type.
551
552 If no :ref:`output_field<output-field>` is specified, it will be inferred from
553 the type of the provided ``value`` for many common types. For example, passing
554 an instance of :py:class:`datetime.datetime` as ``value`` defaults
555 ``output_field`` to :class:`~django.db.models.DateTimeField`.
556
557 ``ExpressionWrapper()`` expressions
558 -----------------------------------
559
560 .. class:: ExpressionWrapper(expression, output_field)
561
562 ``ExpressionWrapper`` surrounds another expression and provides access to
563 properties, such as :ref:`output_field<output-field>`, that may not be
564 available on other expressions. ``ExpressionWrapper`` is necessary when using
565 arithmetic on ``F()`` expressions with different types as described in
566 :ref:`using-f-with-annotations`.
567
568 .. admonition:: Database casting not performed
569
570 ``ExpressionWrapper`` only sets the output field for the ORM and does not
571 perform any database-level casting. To ensure a specific type is returned
572 from the database, use :class:`~django.db.models.functions.Cast` instead.
573
574 Conditional expressions
575 -----------------------
576
577 Conditional expressions allow you to use :keyword:`if` ... :keyword:`elif` ...
578 :keyword:`else` logic in queries. Django natively supports SQL ``CASE``
579 expressions. For more details see :doc:`conditional-expressions`.
580
581 ``Subquery()`` expressions
582 --------------------------
583
584 .. class:: Subquery(queryset, output_field=None)
585
586 You can add an explicit subquery to a ``QuerySet`` using the ``Subquery``
587 expression.
588
589 For example, to annotate each post with the email address of the author of the
590 newest comment on that post:
591
592 .. code-block:: pycon
593
594 >>> from django.db.models import OuterRef, Subquery
595 >>> newest = Comment.objects.filter(post=OuterRef("pk")).order_by("-created_at")
596 >>> Post.objects.annotate(newest_commenter_email=Subquery(newest.values("email")[:1]))
597
598 On PostgreSQL, the SQL looks like:
599
600 .. code-block:: sql
601
602 SELECT "post"."id", (
603 SELECT U0."email"
604 FROM "comment" U0
605 WHERE U0."post_id" = ("post"."id")
606 ORDER BY U0."created_at" DESC LIMIT 1
607 ) AS "newest_commenter_email" FROM "post"
608
609 .. note::
610
611 The examples in this section are designed to show how to force
612 Django to execute a subquery. In some cases it may be possible to
613 write an equivalent queryset that performs the same task more
614 clearly or efficiently.
615
616 Referencing columns from the outer queryset
617 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
618
619 .. class:: OuterRef(field)
620
621 Use ``OuterRef`` when a queryset in a ``Subquery`` needs to refer to a field
622 from the outer query or its transform. It acts like an :class:`F` expression
623 except that the check to see if it refers to a valid field isn't made until the
624 outer queryset is resolved.
625
626 Instances of ``OuterRef`` may be used in conjunction with nested instances
627 of ``Subquery`` to refer to a containing queryset that isn't the immediate
628 parent. For example, this queryset would need to be within a nested pair of
629 ``Subquery`` instances to resolve correctly:
630
631 .. code-block:: pycon
632
633 >>> Book.objects.filter(author=OuterRef(OuterRef("pk")))
634
635 Limiting a subquery to a single column
636 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
637
638 There are times when a single column must be returned from a ``Subquery``, for
639 instance, to use a ``Subquery`` as the target of an ``__in`` lookup. To return
640 all comments for posts published within the last day:
641
642 .. code-block:: pycon
643
644 >>> from datetime import timedelta
645 >>> from django.utils import timezone
646 >>> one_day_ago = timezone.now() - timedelta(days=1)
647 >>> posts = Post.objects.filter(published_at__gte=one_day_ago)
648 >>> Comment.objects.filter(post__in=Subquery(posts.values("pk")))
649
650 In this case, the subquery must use :meth:`~.QuerySet.values`
651 to return only a single column: the primary key of the post.
652
653 Limiting the subquery to a single row
654 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
655
656 To prevent a subquery from returning multiple rows, a slice (``[:1]``) of the
657 queryset is used:
658
659 .. code-block:: pycon
660
661 >>> subquery = Subquery(newest.values("email")[:1])
662 >>> Post.objects.annotate(newest_commenter_email=subquery)
663
664 In this case, the subquery must only return a single column *and* a single
665 row: the email address of the most recently created comment.
666
667 (Using :meth:`~.QuerySet.get` instead of a slice would fail because the
668 ``OuterRef`` cannot be resolved until the queryset is used within a
669 ``Subquery``.)
670
671 ``Exists()`` subqueries
672 ~~~~~~~~~~~~~~~~~~~~~~~
673
674 .. class:: Exists(queryset)
675
676 ``Exists`` is a ``Subquery`` subclass that uses an SQL ``EXISTS`` statement. In
677 many cases it will perform better than a subquery since the database is able to
678 stop evaluation of the subquery when a first matching row is found.
679
680 For example, to annotate each post with whether or not it has a comment from
681 within the last day:
682
683 .. code-block:: pycon
684
685 >>> from django.db.models import Exists, OuterRef
686 >>> from datetime import timedelta
687 >>> from django.utils import timezone
688 >>> one_day_ago = timezone.now() - timedelta(days=1)
689 >>> recent_comments = Comment.objects.filter(
690 ... post=OuterRef("pk"),
691 ... created_at__gte=one_day_ago,
692 ... )
693 >>> Post.objects.annotate(recent_comment=Exists(recent_comments))
694
695 On PostgreSQL, the SQL looks like:
696
697 .. code-block:: sql
698
699 SELECT "post"."id", "post"."published_at", EXISTS(
700 SELECT (1) as "a"
701 FROM "comment" U0
702 WHERE (
703 U0."created_at" >= YYYY-MM-DD HH:MM:SS AND
704 U0."post_id" = "post"."id"
705 )
706 LIMIT 1
707 ) AS "recent_comment" FROM "post"
708
709 It's unnecessary to force ``Exists`` to refer to a single column, since the
710 columns are discarded and a boolean result is returned. Similarly, since
711 ordering is unimportant within an SQL ``EXISTS`` subquery and would only
712 degrade performance, it's automatically removed.
713
714 You can query using ``NOT EXISTS`` with ``~Exists()``.
715
716 Filtering on a ``Subquery()`` or ``Exists()`` expressions
717 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
718
719 ``Subquery()`` that returns a boolean value and ``Exists()`` may be used as a
720 ``condition`` in :class:`~django.db.models.expressions.When` expressions, or to
721 directly filter a queryset:
722
723 .. code-block:: pycon
724
725 >>> recent_comments = Comment.objects.filter(...) # From above
726 >>> Post.objects.filter(Exists(recent_comments))
727
728 This will ensure that the subquery will not be added to the ``SELECT`` columns,
729 which may result in a better performance.
730
731 Using aggregates within a ``Subquery`` expression
732 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
733
734 Aggregates may be used within a ``Subquery``, but they require a specific
735 combination of :meth:`~.QuerySet.filter`, :meth:`~.QuerySet.values`, and
736 :meth:`~.QuerySet.annotate` to get the subquery grouping correct.
737
738 Assuming both models have a ``length`` field, to find posts where the post
739 length is greater than the total length of all combined comments:
740
741 .. code-block:: pycon
742
743 >>> from django.db.models import OuterRef, Subquery, Sum
744 >>> comments = Comment.objects.filter(post=OuterRef("pk")).order_by().values("post")
745 >>> total_comments = comments.annotate(total=Sum("length")).values("total")
746 >>> Post.objects.filter(length__gt=Subquery(total_comments))
747
748 The initial ``filter(...)`` limits the subquery to the relevant parameters.
749 ``order_by()`` removes the default :attr:`~django.db.models.Options.ordering`
750 (if any) on the ``Comment`` model. ``values('post')`` aggregates comments by
751 ``Post``. Finally, ``annotate(...)`` performs the aggregation. The order in
752 which these queryset methods are applied is important. In this case, since the
753 subquery must be limited to a single column, ``values('total')`` is required.
754
755 This is the only way to perform an aggregation within a ``Subquery``, as
756 using :meth:`~.QuerySet.aggregate` attempts to evaluate the queryset (and if
757 there is an ``OuterRef``, this will not be possible to resolve).
758
759 Raw SQL expressions
760 -------------------
761
762 .. currentmodule:: django.db.models.expressions
763
764 .. class:: RawSQL(sql, params, output_field=None)
765
766 Sometimes database expressions can't easily express a complex ``WHERE`` clause.
767 In these edge cases, use the ``RawSQL`` expression. For example:
768
769 .. code-block:: pycon
770
771 >>> from django.db.models.expressions import RawSQL
772 >>> queryset.annotate(val=RawSQL("select col from sometable where othercol = %s", (param,)))
773
774 These extra lookups may not be portable to different database engines (because
775 you're explicitly writing SQL code) and violate the DRY principle, so you
776 should avoid them if possible.
777
778 ``RawSQL`` expressions can also be used as the target of ``__in`` filters:
779
780 .. code-block:: pycon
781
782 >>> queryset.filter(id__in=RawSQL("select id from sometable where col = %s", (param,)))
783
784 .. warning::
785
786 To protect against `SQL injection attacks
787 <https://en.wikipedia.org/wiki/SQL_injection>`_, you must escape any
788 parameters that the user can control by using ``params``. ``params`` is a
789 required argument to force you to acknowledge that you're not interpolating
790 your SQL with user-provided data.
791
792 You also must not quote placeholders in the SQL string. This example is
793 vulnerable to SQL injection because of the quotes around ``%s``::
794
795 RawSQL("select col from sometable where othercol = '%s'") # unsafe!
796
797 You can read more about how Django's :ref:`SQL injection protection
798 <sql-injection-protection>` works.
799
800 Window functions
801 ----------------
802
803 Window functions provide a way to apply functions on partitions. Unlike a
804 normal aggregation function which computes a final result for each set defined
805 by the group by, window functions operate on :ref:`frames <window-frames>` and
806 partitions, and compute the result for each row.
807
808 You can specify multiple windows in the same query which in Django ORM would be
809 equivalent to including multiple expressions in a :doc:`QuerySet.annotate()
810 </topics/db/aggregation>` call. The ORM doesn't make use of named windows,
811 instead they are part of the selected columns.
812
813 .. class:: Window(expression, partition_by=None, order_by=None, frame=None, output_field=None)
814
815 .. attribute:: template
816
817 Defaults to ``%(expression)s OVER (%(window)s)``. If only the
818 ``expression`` argument is provided, the window clause will be blank.
819
820 The ``Window`` class is the main expression for an ``OVER`` clause.
821
822 The ``expression`` argument is either a :ref:`window function
823 <window-functions>`, an :ref:`aggregate function <aggregation-functions>`, or
824 an expression that's compatible in a window clause.
825
826 The ``partition_by`` argument accepts an expression or a sequence of
827 expressions (column names should be wrapped in an ``F``-object) that control
828 the partitioning of the rows. Partitioning narrows which rows are used to
829 compute the result set.
830
831 The :ref:`output_field<output-field>` is specified either as an argument or by
832 the expression.
833
834 The ``order_by`` argument accepts an expression on which you can call
835 :meth:`~django.db.models.Expression.asc` and
836 :meth:`~django.db.models.Expression.desc`, a string of a field name (with an
837 optional ``"-"`` prefix which indicates descending order), or a tuple or list
838 of strings and/or expressions. The ordering controls the order in which the
839 expression is applied. For example, if you sum over the rows in a partition,
840 the first result is the value of the first row, the second is the sum of first
841 and second row.
842
843 The ``frame`` parameter specifies which other rows that should be used in the
844 computation. See :ref:`window-frames` for details.
845
846 For example, to annotate each movie with the average rating for the movies by
847 the same studio in the same genre and release year:
848
849 .. code-block:: pycon
850
851 >>> from django.db.models import Avg, F, Window
852 >>> Movie.objects.annotate(
853 ... avg_rating=Window(
854 ... expression=Avg("rating"),
855 ... partition_by=[F("studio"), F("genre")],
856 ... order_by="released__year",
857 ... ),
858 ... )
859
860 This allows you to check if a movie is rated better or worse than its peers.
861
862 You may want to apply multiple expressions over the same window, i.e., the
863 same partition and frame. For example, you could modify the previous example
864 to also include the best and worst rating in each movie's group (same studio,
865 genre, and release year) by using three window functions in the same query. The
866 partition and ordering from the previous example is extracted into a dictionary
867 to reduce repetition:
868
869 .. code-block:: pycon
870
871 >>> from django.db.models import Avg, F, Max, Min, Window
872 >>> window = {
873 ... "partition_by": [F("studio"), F("genre")],
874 ... "order_by": "released__year",
875 ... }
876 >>> Movie.objects.annotate(
877 ... avg_rating=Window(
878 ... expression=Avg("rating"),
879 ... **window,
880 ... ),
881 ... best=Window(
882 ... expression=Max("rating"),
883 ... **window,
884 ... ),
885 ... worst=Window(
886 ... expression=Min("rating"),
887 ... **window,
888 ... ),
889 ... )
890
891 Filtering against window functions is supported as long as lookups are not
892 disjunctive (not using ``OR`` or ``XOR`` as a connector) and against a queryset
893 performing aggregation.
894
895 For example, a query that relies on aggregation and has an ``OR``-ed filter
896 against a window function and a field is not supported. Applying combined
897 predicates post-aggregation could cause rows that would normally be excluded
898 from groups to be included:
899
900 .. code-block:: pycon
901
902 >>> qs = Movie.objects.annotate(
903 ... category_rank=Window(Rank(), partition_by="category", order_by="-rating"),
904 ... scenes_count=Count("actors"),
905 ... ).filter(Q(category_rank__lte=3) | Q(title__contains="Batman"))
906 >>> list(qs)
907 NotImplementedError: Heterogeneous disjunctive predicates against window functions
908 are not implemented when performing conditional aggregation.
909
910 Among Django's built-in database backends, MySQL, PostgreSQL, and Oracle
911 support window expressions. Support for different window expression features
912 varies among the different databases. For example, the options in
913 :meth:`~django.db.models.Expression.asc` and
914 :meth:`~django.db.models.Expression.desc` may not be supported. Consult the
915 documentation for your database as needed.
916
917 .. _window-frames:
918
919 Frames
920 ~~~~~~
921
922 For a window frame, you can choose either a range-based sequence of rows or an
923 ordinary sequence of rows.
924
925 .. class:: ValueRange(start=None, end=None, exclusion=None)
926
927 .. attribute:: frame_type
928
929 This attribute is set to ``'RANGE'``.
930
931 PostgreSQL has limited support for ``ValueRange`` and only supports use of
932 the standard start and end points, such as ``CURRENT ROW`` and ``UNBOUNDED
933 FOLLOWING``.
934
935 .. versionchanged:: 5.1
936
937 The ``exclusion`` argument was added.
938
939 .. class:: RowRange(start=None, end=None, exclusion=None)
940
941 .. attribute:: frame_type
942
943 This attribute is set to ``'ROWS'``.
944
945 .. versionchanged:: 5.1
946
947 The ``exclusion`` argument was added.
948
949 Both classes return SQL with the template:
950
951 .. code-block:: sql
952
953 %(frame_type)s BETWEEN %(start)s AND %(end)s
954
955 .. class:: WindowFrameExclusion
956
957 .. versionadded:: 5.1
958
959 .. attribute:: CURRENT_ROW
960
961 .. attribute:: GROUP
962
963 .. attribute:: TIES
964
965 .. attribute:: NO_OTHERS
966
967 The ``exclusion`` argument allows excluding rows
968 (:attr:`~WindowFrameExclusion.CURRENT_ROW`), groups
969 (:attr:`~WindowFrameExclusion.GROUP`), and ties
970 (:attr:`~WindowFrameExclusion.TIES`) from the window frames on supported
971 databases:
972
973 .. code-block:: sql
974
975 %(frame_type)s BETWEEN %(start)s AND %(end)s EXCLUDE %(exclusion)s
976
977 Frames narrow the rows that are used for computing the result. They shift from
978 some start point to some specified end point. Frames can be used with and
979 without partitions, but it's often a good idea to specify an ordering of the
980 window to ensure a deterministic result. In a frame, a peer in a frame is a row
981 with an equivalent value, or all rows if an ordering clause isn't present.
982
983 The default starting point for a frame is ``UNBOUNDED PRECEDING`` which is the
984 first row of the partition. The end point is always explicitly included in the
985 SQL generated by the ORM and is by default ``UNBOUNDED FOLLOWING``. The default
986 frame includes all rows from the partition to the last row in the set.
987
988 The accepted values for the ``start`` and ``end`` arguments are ``None``, an
989 integer, or zero. A negative integer for ``start`` results in ``N PRECEDING``,
990 while ``None`` yields ``UNBOUNDED PRECEDING``. In ``ROWS`` mode, a positive
991 integer can be used for ``start`` resulting in ``N FOLLOWING``. Positive
992 integers are accepted for ``end`` and results in ``N FOLLOWING``. In ``ROWS``
993 mode, a negative integer can be used for ``end`` resulting in ``N PRECEDING``.
994 For both ``start`` and ``end``, zero will return ``CURRENT ROW``.
995
996 There's a difference in what ``CURRENT ROW`` includes. When specified in
997 ``ROWS`` mode, the frame starts or ends with the current row. When specified in
998 ``RANGE`` mode, the frame starts or ends at the first or last peer according to
999 the ordering clause. Thus, ``RANGE CURRENT ROW`` evaluates the expression for
1000 rows which have the same value specified by the ordering. Because the template
1001 includes both the ``start`` and ``end`` points, this may be expressed with::
1002
1003 ValueRange(start=0, end=0)
1004
1005 If a movie's "peers" are described as movies released by the same studio in the
1006 same genre in the same year, this ``RowRange`` example annotates each movie
1007 with the average rating of a movie's two prior and two following peers:
1008
1009 .. code-block:: pycon
1010
1011 >>> from django.db.models import Avg, F, RowRange, Window
1012 >>> Movie.objects.annotate(
1013 ... avg_rating=Window(
1014 ... expression=Avg("rating"),
1015 ... partition_by=[F("studio"), F("genre")],
1016 ... order_by="released__year",
1017 ... frame=RowRange(start=-2, end=2),
1018 ... ),
1019 ... )
1020
1021 If the database supports it, you can specify the start and end points based on
1022 values of an expression in the partition. If the ``released`` field of the
1023 ``Movie`` model stores the release month of each movie, this ``ValueRange``
1024 example annotates each movie with the average rating of a movie's peers
1025 released between twelve months before and twelve months after each movie:
1026
1027 .. code-block:: pycon
1028
1029 >>> from django.db.models import Avg, F, ValueRange, Window
1030 >>> Movie.objects.annotate(
1031 ... avg_rating=Window(
1032 ... expression=Avg("rating"),
1033 ... partition_by=[F("studio"), F("genre")],
1034 ... order_by="released__year",
1035 ... frame=ValueRange(start=-12, end=12),
1036 ... ),
1037 ... )
1038
1039 .. versionchanged:: 5.1
1040
1041 Support for positive integer ``start`` and negative integer ``end`` was
1042 added for ``RowRange``.
1043
1044 .. currentmodule:: django.db.models
1045
1046 Technical Information
1047 =====================
1048
1049 Below you'll find technical implementation details that may be useful to
1050 library authors. The technical API and examples below will help with
1051 creating generic query expressions that can extend the built-in functionality
1052 that Django provides.
1053
1054 Expression API
1055 --------------
1056
1057 Query expressions implement the :ref:`query expression API <query-expression>`,
1058 but also expose a number of extra methods and attributes listed below. All
1059 query expressions must inherit from ``Expression()`` or a relevant
1060 subclass.
1061
1062 When a query expression wraps another expression, it is responsible for
1063 calling the appropriate methods on the wrapped expression.
1064
1065 .. class:: Expression
1066
1067 .. attribute:: allowed_default
1068
1069 Tells Django that this expression can be used in
1070 :attr:`Field.db_default`. Defaults to ``False``.
1071
1072 .. attribute:: constraint_validation_compatible
1073
1074 .. versionadded:: 5.1
1075
1076 Tells Django that this expression can be used during a constraint
1077 validation. Expressions with ``constraint_validation_compatible`` set
1078 to ``False`` must have only one source expression. Defaults to
1079 ``True``.
1080
1081 .. attribute:: contains_aggregate
1082
1083 Tells Django that this expression contains an aggregate and that a
1084 ``GROUP BY`` clause needs to be added to the query.
1085
1086 .. attribute:: contains_over_clause
1087
1088 Tells Django that this expression contains a
1089 :class:`~django.db.models.expressions.Window` expression. It's used,
1090 for example, to disallow window function expressions in queries that
1091 modify data.
1092
1093 .. attribute:: filterable
1094
1095 Tells Django that this expression can be referenced in
1096 :meth:`.QuerySet.filter`. Defaults to ``True``.
1097
1098 .. attribute:: window_compatible
1099
1100 Tells Django that this expression can be used as the source expression
1101 in :class:`~django.db.models.expressions.Window`. Defaults to
1102 ``False``.
1103
1104 .. attribute:: empty_result_set_value
1105
1106 Tells Django which value should be returned when the expression is used
1107 to apply a function over an empty result set. Defaults to
1108 :py:data:`NotImplemented` which forces the expression to be computed on
1109 the database.
1110
1111 .. attribute:: set_returning
1112
1113 .. versionadded:: 5.2
1114
1115 Tells Django that this expression contains a set-returning function,
1116 enforcing subquery evaluation. It's used, for example, to allow some
1117 Postgres set-returning functions (e.g. ``JSONB_PATH_QUERY``,
1118 ``UNNEST``, etc.) to skip optimization and be properly evaluated when
1119 annotations spawn rows themselves. Defaults to ``False``.
1120
1121 .. attribute:: allows_composite_expressions
1122
1123 .. versionadded:: 5.2
1124
1125 Tells Django that this expression allows composite expressions, for
1126 example, to support :ref:`composite primary keys
1127 <cpk-and-database-functions>`. Defaults to ``False``.
1128
1129 .. method:: resolve_expression(query=None, allow_joins=True, reuse=None, summarize=False, for_save=False)
1130
1131 Provides the chance to do any preprocessing or validation of
1132 the expression before it's added to the query. ``resolve_expression()``
1133 must also be called on any nested expressions. A ``copy()`` of ``self``
1134 should be returned with any necessary transformations.
1135
1136 ``query`` is the backend query implementation.
1137
1138 ``allow_joins`` is a boolean that allows or denies the use of
1139 joins in the query.
1140
1141 ``reuse`` is a set of reusable joins for multi-join scenarios.
1142
1143 ``summarize`` is a boolean that, when ``True``, signals that the
1144 query being computed is a terminal aggregate query.
1145
1146 ``for_save`` is a boolean that, when ``True``, signals that the query
1147 being executed is performing a create or update.
1148
1149 .. method:: get_source_expressions()
1150
1151 Returns an ordered list of inner expressions. For example:
1152
1153 .. code-block:: pycon
1154
1155 >>> Sum(F("foo")).get_source_expressions()
1156 [F('foo')]
1157
1158 .. method:: set_source_expressions(expressions)
1159
1160 Takes a list of expressions and stores them such that
1161 ``get_source_expressions()`` can return them.
1162
1163 .. method:: relabeled_clone(change_map)
1164
1165 Returns a clone (copy) of ``self``, with any column aliases relabeled.
1166 Column aliases are renamed when subqueries are created.
1167 ``relabeled_clone()`` should also be called on any nested expressions
1168 and assigned to the clone.
1169
1170 ``change_map`` is a dictionary mapping old aliases to new aliases.
1171
1172 Example::
1173
1174 def relabeled_clone(self, change_map):
1175 clone = copy.copy(self)
1176 clone.expression = self.expression.relabeled_clone(change_map)
1177 return clone
1178
1179 .. method:: convert_value(value, expression, connection)
1180
1181 A hook allowing the expression to coerce ``value`` into a more
1182 appropriate type.
1183
1184 ``expression`` is the same as ``self``.
1185
1186 .. method:: get_group_by_cols()
1187
1188 Responsible for returning the list of columns references by
1189 this expression. ``get_group_by_cols()`` should be called on any
1190 nested expressions. ``F()`` objects, in particular, hold a reference
1191 to a column.
1192
1193 .. method:: asc(nulls_first=None, nulls_last=None)
1194
1195 Returns the expression ready to be sorted in ascending order.
1196
1197 ``nulls_first`` and ``nulls_last`` define how null values are sorted.
1198 See :ref:`using-f-to-sort-null-values` for example usage.
1199
1200 .. method:: desc(nulls_first=None, nulls_last=None)
1201
1202 Returns the expression ready to be sorted in descending order.
1203
1204 ``nulls_first`` and ``nulls_last`` define how null values are sorted.
1205 See :ref:`using-f-to-sort-null-values` for example usage.
1206
1207 .. method:: reverse_ordering()
1208
1209 Returns ``self`` with any modifications required to reverse the sort
1210 order within an ``order_by`` call. As an example, an expression
1211 implementing ``NULLS LAST`` would change its value to be
1212 ``NULLS FIRST``. Modifications are only required for expressions that
1213 implement sort order like ``OrderBy``. This method is called when
1214 :meth:`~django.db.models.query.QuerySet.reverse()` is called on a
1215 queryset.
1216
1217 Writing your own Query Expressions
1218 ----------------------------------
1219
1220 You can write your own query expression classes that use, and can integrate
1221 with, other query expressions. Let's step through an example by writing an
1222 implementation of the ``COALESCE`` SQL function, without using the built-in
1223 :ref:`Func() expressions <func-expressions>`.
1224
1225 The ``COALESCE`` SQL function is defined as taking a list of columns or
1226 values. It will return the first column or value that isn't ``NULL``.
1227
1228 We'll start by defining the template to be used for SQL generation and
1229 an ``__init__()`` method to set some attributes::
1230
1231 from django.db.models import Expression
1232
1233
1234 class Coalesce(Expression):
1235 template = "COALESCE( %(expressions)s )"
1236
1237 def __init__(self, expressions, output_field):
1238 super().__init__(output_field=output_field)
1239 if len(expressions) < 2:
1240 raise ValueError("expressions must have at least 2 elements")
1241 for expression in expressions:
1242 if not hasattr(expression, "resolve_expression"):
1243 raise TypeError("%r is not an Expression" % expression)
1244 self.expressions = expressions
1245
1246 We do some basic validation on the parameters, including requiring at least 2
1247 columns or values, and ensuring they are expressions. We are requiring
1248 :ref:`output_field<output-field>` here so that Django knows what kind of model
1249 field to assign the eventual result to.
1250
1251 Now we implement the preprocessing and validation. Since we do not have
1252 any of our own validation at this point, we delegate to the nested
1253 expressions::
1254
1255 def resolve_expression(
1256 self, query=None, allow_joins=True, reuse=None, summarize=False, for_save=False
1257 ):
1258 c = self.copy()
1259 c.is_summary = summarize
1260 for pos, expression in enumerate(self.expressions):
1261 c.expressions[pos] = expression.resolve_expression(
1262 query, allow_joins, reuse, summarize, for_save
1263 )
1264 return c
1265
1266 Next, we write the method responsible for generating the SQL::
1267
1268 def as_sql(self, compiler, connection, template=None):
1269 sql_expressions, sql_params = [], []
1270 for expression in self.expressions:
1271 sql, params = compiler.compile(expression)
1272 sql_expressions.append(sql)
1273 sql_params.extend(params)
1274 template = template or self.template
1275 data = {"expressions": ",".join(sql_expressions)}
1276 return template % data, sql_params
1277
1278
1279 def as_oracle(self, compiler, connection):
1280 """
1281 Example of vendor specific handling (Oracle in this case).
1282 Let's make the function name lowercase.
1283 """
1284 return self.as_sql(compiler, connection, template="coalesce( %(expressions)s )")
1285
1286 ``as_sql()`` methods can support custom keyword arguments, allowing
1287 ``as_vendorname()`` methods to override data used to generate the SQL string.
1288 Using ``as_sql()`` keyword arguments for customization is preferable to
1289 mutating ``self`` within ``as_vendorname()`` methods as the latter can lead to
1290 errors when running on different database backends. If your class relies on
1291 class attributes to define data, consider allowing overrides in your
1292 ``as_sql()`` method.
1293
1294 We generate the SQL for each of the ``expressions`` by using the
1295 ``compiler.compile()`` method, and join the result together with commas.
1296 Then the template is filled out with our data and the SQL and parameters
1297 are returned.
1298
1299 We've also defined a custom implementation that is specific to the Oracle
1300 backend. The ``as_oracle()`` function will be called instead of ``as_sql()``
1301 if the Oracle backend is in use.
1302
1303 Finally, we implement the rest of the methods that allow our query expression
1304 to play nice with other query expressions::
1305
1306 def get_source_expressions(self):
1307 return self.expressions
1308
1309
1310 def set_source_expressions(self, expressions):
1311 self.expressions = expressions
1312
1313 Let's see how it works:
1314
1315 .. code-block:: pycon
1316
1317 >>> from django.db.models import F, Value, CharField
1318 >>> qs = Company.objects.annotate(
1319 ... tagline=Coalesce(
1320 ... [F("motto"), F("ticker_name"), F("description"), Value("No Tagline")],
1321 ... output_field=CharField(),
1322 ... )
1323 ... )
1324 >>> for c in qs:
1325 ... print("%s: %s" % (c.name, c.tagline))
1326 ...
1327 Google: Do No Evil
1328 Apple: AAPL
1329 Yahoo: Internet Company
1330 Django Software Foundation: No Tagline
1331
1332 .. _avoiding-sql-injection-in-query-expressions:
1333
1334 Avoiding SQL injection
1335 ~~~~~~~~~~~~~~~~~~~~~~
1336
1337 Since a ``Func``'s keyword arguments for ``__init__()`` (``**extra``) and
1338 ``as_sql()`` (``**extra_context``) are interpolated into the SQL string rather
1339 than passed as query parameters (where the database driver would escape them),
1340 they must not contain untrusted user input.
1341
1342 For example, if ``substring`` is user-provided, this function is vulnerable to
1343 SQL injection::
1344
1345 from django.db.models import Func
1346
1347
1348 class Position(Func):
1349 function = "POSITION"
1350 template = "%(function)s('%(substring)s' in %(expressions)s)"
1351
1352 def __init__(self, expression, substring):
1353 # substring=substring is an SQL injection vulnerability!
1354 super().__init__(expression, substring=substring)
1355
1356 This function generates an SQL string without any parameters. Since
1357 ``substring`` is passed to ``super().__init__()`` as a keyword argument, it's
1358 interpolated into the SQL string before the query is sent to the database.
1359
1360 Here's a corrected rewrite::
1361
1362 class Position(Func):
1363 function = "POSITION"
1364 arg_joiner = " IN "
1365
1366 def __init__(self, expression, substring):
1367 super().__init__(substring, expression)
1368
1369 With ``substring`` instead passed as a positional argument, it'll be passed as
1370 a parameter in the database query.
1371
1372 Adding support in third-party database backends
1373 -----------------------------------------------
1374
1375 If you're using a database backend that uses a different SQL syntax for a
1376 certain function, you can add support for it by monkey patching a new method
1377 onto the function's class.
1378
1379 Let's say we're writing a backend for Microsoft's SQL Server which uses the SQL
1380 ``LEN`` instead of ``LENGTH`` for the :class:`~functions.Length` function.
1381 We'll monkey patch a new method called ``as_sqlserver()`` onto the ``Length``
1382 class::
1383
1384 from django.db.models.functions import Length
1385
1386
1387 def sqlserver_length(self, compiler, connection):
1388 return self.as_sql(compiler, connection, function="LEN")
1389
1390
1391 Length.as_sqlserver = sqlserver_length
1392
1393 You can also customize the SQL using the ``template`` parameter of ``as_sql()``.
1394
1395 We use ``as_sqlserver()`` because ``django.db.connection.vendor`` returns
1396 ``sqlserver`` for the backend.
1397
1398 Third-party backends can register their functions in the top level
1399 ``__init__.py`` file of the backend package or in a top level ``expressions.py``
1400 file (or package) that is imported from the top level ``__init__.py``.
1401
1402 For user projects wishing to patch the backend that they're using, this code
1403 should live in an :meth:`AppConfig.ready()<django.apps.AppConfig.ready>` method.