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