Выражения запросов

Выражения запросов описывают значение или вычисление, которое может быть использовано в рамках обновления, создания, фильтрации, сортировки, аннотирования или агрегирования. Когда выражение выводит булево значение, оно может быть использовано непосредственно в фильтрах. Существует ряд встроенных выражений (документированных ниже), которые могут помочь вам создавать запросы. Выражения могут быть объединены или в некоторых случаях вложены, чтобы сформировать более сложные вычисления.

Поддерживаемые арифметические операции

Django поддерживает отрицание, сложение, вычитание, умножение, деление, модуль арифметики и операцию возведения в степень над выражениями запросов, используя Python-константы, переменные и даже другие выражения.

Некоторые примеры

from django.db.models import Count, F, Value
from django.db.models.functions import Length, Upper
from django.db.models.lookups import GreaterThan

# Find companies that have more employees than chairs.
Company.objects.filter(num_employees__gt=F('num_chairs'))

# Find companies that have at least twice as many employees
# as chairs. Both the querysets below are equivalent.
Company.objects.filter(num_employees__gt=F('num_chairs') * 2)
Company.objects.filter(
    num_employees__gt=F('num_chairs') + F('num_chairs'))

# How many chairs are needed for each company to seat all employees?
>>> company = Company.objects.filter(
...    num_employees__gt=F('num_chairs')).annotate(
...    chairs_needed=F('num_employees') - F('num_chairs')).first()
>>> company.num_employees
120
>>> company.num_chairs
50
>>> company.chairs_needed
70

# Create a new company using expressions.
>>> company = Company.objects.create(name='Google', ticker=Upper(Value('goog')))
# Be sure to refresh it if you need to access the field.
>>> company.refresh_from_db()
>>> company.ticker
'GOOG'

# Annotate models with an aggregated value. Both forms
# below are equivalent.
Company.objects.annotate(num_products=Count('products'))
Company.objects.annotate(num_products=Count(F('products')))

# Aggregates can contain complex computations also
Company.objects.annotate(num_offerings=Count(F('products') + F('services')))

# Expressions can also be used in order_by(), either directly
Company.objects.order_by(Length('name').asc())
Company.objects.order_by(Length('name').desc())
# or using the double underscore lookup syntax.
from django.db.models import CharField
from django.db.models.functions import Length
CharField.register_lookup(Length)
Company.objects.order_by('name__length')

# Boolean expression can be used directly in filters.
from django.db.models import Exists
Company.objects.filter(
    Exists(Employee.objects.filter(company=OuterRef('pk'), salary__gt=10))
)

# Lookup expressions can also be used directly in filters
Company.objects.filter(GreaterThan(F('num_employees'), F('num_chairs')))
# or annotations.
Company.objects.annotate(
    need_chairs=GreaterThan(F('num_employees'), F('num_chairs')),
)

Встроенные выражения

F() выражения

class F

Объект F() представляет значение поля модели, преобразованное значение поля модели или аннотированный столбец. Он позволяет ссылаться на значения полей модели и выполнять операции с базой данных, не извлекая их фактически из базы данных в память Python.

Вместо этого Django использует объект F() для генерации SQL-выражения, описывающего необходимую операцию на уровне базы данных.

Попробуем это на примере. Обычно можно сделать что-то вроде этого:

# Tintin filed a news story!
reporter = Reporters.objects.get(name='Tintin')
reporter.stories_filed += 1
reporter.save()

Здесь мы извлекли значение reporter.stories_filed из базы данных в память и обработали его с помощью знакомых Python-операторов, а затем сохранили объект обратно в базу данных. Но вместо этого мы также могли бы сделать:

from django.db.models import F

reporter = Reporters.objects.get(name='Tintin')
reporter.stories_filed = F('stories_filed') + 1
reporter.save()

Хотя reporter.stories_filed = F('stories_filed') + 1 выглядит как обычная Python-присваивание значения атрибуту экземпляра, на самом деле это SQL-конструктор, описывающий операцию в базе данных.

Когда Django сталкивается с экземпляром F(), он переопределяет стандартные Python-операторы, чтобы создать инкапсулированное SQL-выражение; в этом случае, выражение, которое инструктирует базу данных о том, чтобы увеличить поле базы данных, представленное reporter.stories_filed.

Любое значение, которое было или есть в reporter.stories_filed, Python никогда не узнает о нём — с этим полностью работает база данных. Всё, что делает Python через класс Django F(), — это создание синтаксиса SQL для ссылки на поле и описание операции.

Для доступа к новому сохранённому таким образом значению объект необходимо перезагрузить:

reporter = Reporters.objects.get(pk=reporter.pk)
# Or, more succinctly:
reporter.refresh_from_db()

Помимо использования в операциях с отдельными экземплярами, как показано выше, F() может использоваться с QuerySets экземпляров объектов с update(). Это уменьшает две запросы, которые мы использовали выше — get() и save() — до одной:

reporter = Reporters.objects.filter(name='Tintin')
reporter.update(stories_filed=F('stories_filed') + 1)

Мы также можем использовать update() для увеличения значения поля для нескольких объектов — что может быть гораздо быстрее, чем извлечение всех их в Python из базы данных, итерация по ним, увеличение значения поля каждого из них и сохранение каждого из них обратно в базу данных:

Reporter.objects.all().update(stories_filed=F('stories_filed') + 1)

F() поэтому может предоставить преимущества производительности, за счёт:

• передачи работы базе данных, а не Python
• уменьшения количества запросов, необходимых для некоторых операций

Избежание гонок с помощью F()

Ещё одним полезным преимуществом F() является то, что наличие базы данных — а не Python — обновляет значение поля, предотвращает возникновение гонки.

Если два потока Python выполняют код в первом примере выше, один поток может извлечь, увеличить и сохранить значение поля после того, как другой извлёк его из базы данных. Значение, которое сохранит второй поток, будет основано на исходном значении; работа первого потока будет утеряна.

Если база данных отвечает за обновление поля, процесс более надёжен: он будет обновлять поле только на основе значения поля в базе данных, когда выполняется save() или update(), а не на основе его значения, когда экземпляр был извлечён.

F() присвоения сохраняются после Model.save()

F() объекты, присвоенные полям модели, сохраняются после сохранения экземпляра модели и будут применяться при каждом save(). Например:

reporter = Reporters.objects.get(name='Tintin')
reporter.stories_filed = F('stories_filed') + 1
reporter.save()

reporter.name = 'Tintin Jr.'
reporter.save()

stories_filed будет обновлён дважды в этом случае. Если вначале он 1, окончательное значение будет 3. Это сохранение можно избежать, перезагрузив объект модели после сохранения, например, с помощью refresh_from_db().

Использование F() в фильтрах

F() также очень полезно в фильтрах QuerySet, где они позволяют фильтровать набор объектов по критериям, основанным на значениях их полей, а не на Python-значениях.

Это документировано в использование выражений F() в запросах.

Использование F() с аннотациями

F() может использоваться для создания динамических полей в ваших моделях путём объединения различных полей с арифметикой:

company = Company.objects.annotate(
    chairs_needed=F('num_employees') - F('num_chairs'))

Если поля, которые вы объединяете, имеют разные типы, вам нужно указать Django, какого типа будет возвращаемое поле. Поскольку F() не поддерживает output_field, вам необходимо обернуть выражение в ExpressionWrapper:

from django.db.models import DateTimeField, ExpressionWrapper, F

Ticket.objects.annotate(
    expires=ExpressionWrapper(
        F('active_at') + F('duration'), output_field=DateTimeField()))

При ссылке на реляционные поля, такие как ForeignKey, F() возвращает значение первичного ключа, а не экземпляр модели:

>> car = Company.objects.annotate(built_by=F('manufacturer'))[0]
>> car.manufacturer
<Manufacturer: Toyota>
>> car.built_by
3

Использование F() для сортировки нулевых значений

Используйте F() и ключевое слово nulls_first или nulls_last для Expression.asc() или desc() для управления сортировкой нулевых значений поля. По умолчанию сортировка зависит от вашей базы данных.

Например, чтобы отсортировать компании, с которыми не было связи (last_contacted является null), после компаний, с которыми была связь:

from django.db.models import F
Company.objects.order_by(F('last_contacted').desc(nulls_last=True))

Func() выражения

Func() выражения — это базовый тип всех выражений, включающих функции базы данных, такие как COALESCE и LOWER, или агрегаты, такие как SUM. Они могут использоваться непосредственно:

from django.db.models import F, Func

queryset.annotate(field_lower=Func(F('field'), function='LOWER'))

или они могут быть использованы для построения библиотеки функций базы данных:

class Lower(Func):
    function = 'LOWER'

queryset.annotate(field_lower=Lower('field'))

Но в обоих случаях будет получен набор запросов, где каждая модель будет аннотирована дополнительным атрибутом field_lower, полученным приблизительно из следующего SQL:

SELECT
    ...
    LOWER("db_table"."field") as "field_lower"

См. Функции базы данных для списка встроенных функций базы данных.

API Func выглядит следующим образом:

class Func(*expressions, **extra)

function

Атрибут класса, описывающий функцию, которая будет сгенерирована. В частности, function будет интерполирован в качестве замены function в template. По умолчанию None.

template

Атрибут класса, в виде строки форматирования, описывающий SQL, который генерируется для этой функции. По умолчанию '%(function)s(%(expressions)s)'.

Если вы строите SQL, как strftime('%W', 'date') и вам нужна буквальная % в запросе, удваивайте её (%%%%) в атрибуте template, так как строка интерполируется дважды: один раз во время интерполяции шаблона в as_sql(), и второй раз в SQL интерполяции с параметрами запроса в курсоре базы данных.

arg_joiner

Атрибут класса, обозначающий символ, используемый для объединения списка expressions вместе. По умолчанию ', '.

arity

Атрибут класса, обозначающий количество аргументов, принимаемых функцией. Если этот атрибут задан, а функция вызывается с другим количеством выражений, TypeError будет поднята. По умолчанию None.

as_sql(compiler, connection, function=None, template=None, arg_joiner=None, **extra_context)

Генерирует фрагмент SQL для функции базы данных. Возвращает кортеж (sql, params), где sql — строка SQL, а params — список или кортеж параметров запроса.

Методы as_vendor() должны использовать function, template, arg_joiner, и любые другие **extra_context параметры для настройки SQL по мере необходимости. Например:

django/db/models/functions.py

class ConcatPair(Func):
    ...
    function = 'CONCAT'
    ...

    def as_mysql(self, compiler, connection, **extra_context):
        return super().as_sql(
            compiler, connection,
            function='CONCAT_WS',
            template="%(function)s('', %(expressions)s)",
            **extra_context
        )

Чтобы избежать уязвимости SQL-инъекции, extra_context не должно содержать ненадёжные данные пользователя, так как эти значения интерполируются в строку SQL, а не передаются как параметры запроса, где драйвер базы данных их экранировал бы.

Аргумент *expressions — список позиционных выражений, к которым будет применена функция. Выражения будут преобразованы в строки, объединены символом arg_joiner, а затем интерполированы в template в качестве замены expressions.

Позиционные аргументы могут быть выражениями или значениями Python. Строки предполагаются ссылками на столбцы и будут обернуты в F() выражения, а другие значения — в Value() выражения.

Ключевые параметры **extra — пары key=value, которые можно интерполировать в атрибут template.

Для предотвращения уязвимости SQL-инъекции, extra не должно содержать ненадёжные данные пользователя, поскольку эти значения интерполируются в строку SQL, а не передаются в качестве параметров запроса, где драйвер базы данных их экранирует.

Ключевые слова function, template, и arg_joiner могут использоваться для замены атрибутов с тем же именем, без необходимости определения собственного класса. output_field может использоваться для определения ожидаемого типа возвращаемого значения.

Aggregate() выражения

Агрегационное выражение — это специальный случай выражения Func() выражения, которое сообщает запросу, что необходима GROUP BY команда. Все функции агрегации, такие как Sum() и Count(), унаследованы от Aggregate().

Поскольку Aggregate — это выражения и обертывают выражения, вы можете представлять некоторые сложные вычисления:

from django.db.models import Count

Company.objects.annotate(
    managers_required=(Count('num_employees') / 4) + Count('num_managers'))

API Aggregate выглядит следующим образом:

class Aggregate(*expressions, output_field=None, distinct=False, filter=None, default=None, **extra)

template

Атрибут класса, в виде строки форматирования, описывающий SQL, который генерируется для этого агрегата. По умолчанию '%(function)s(%(distinct)s%(expressions)s)'.

function

Атрибут класса, описывающий функцию агрегации, которая будет сгенерирована. Конкретно, function будет интерполирован в качестве замены function в template. По умолчанию None.

window_compatible

По умолчанию True поскольку большинство функций агрегации могут использоваться в качестве исходного выражения в Window.

allow_distinct

Атрибут класса, определяющий, разрешено ли передавать ключевой аргумент distinct. Если установлено значение False (по умолчанию), TypeError будет поднят, если distinct=True передан.

empty_result_set_value

Новое в Django 4.0.

Переопределите empty_result_set_value на None, так как большинство функций агрегации возвращают NULL при применении к пустому результату.

Позиционные аргументы expressions могут включать выражения, преобразования поля модели или имена полей модели. Они будут преобразованы в строку и использованы как expressions заполнитель в template.

Аргумент output_field требует экземпляра поля модели, такого как IntegerField() или BooleanField(), в который Django загрузит значение после извлечения его из базы данных. Как правило, при создании экземпляра поля модели не нужны никакие аргументы, так как любые аргументы, относящиеся к проверке данных (max_length, max_digits, и т. д.), не будут применены к выходному значению выражения.

Обратите внимание, что output_field требуется только тогда, когда Django не может определить тип поля результата. Сложные выражения, которые смешивают типы полей, должны определить желаемый output_field. Например, при сложении IntegerField() и FloatField() вместе, вероятно, нужно определять output_field=FloatField().

Аргумент distinct определяет, должна ли функция агрегации вызываться для каждого уникального значения expressions (или набора значений для нескольких expressions). Этот аргумент поддерживается только для агрегатов, у которых allow_distinct установлено в значение True.

Аргумент filter принимает Q object, используемый для фильтрации строк, которые агрегируются. См. Условную агрегацию и Фильтрование аннотаций для примера использования.

Аргумент default принимает значение, которое будет передано вместе с агрегатом в Coalesce. Это полезно для указания значения, возвращаемого вместо None, когда в наборе запросов (или группировании) нет записей.

Ключевые параметры **extra — пары key=value, которые можно интерполировать в атрибут template.

Создание собственных функций агрегации

Вы также можете создать собственные функции агрегации. Как минимум, вам нужно определить function, но вы также можете полностью настроить генерируемый SQL. Вот краткий пример:

from django.db.models import Aggregate

class Sum(Aggregate):
    # Supports SUM(ALL field).
    function = 'SUM'
    template = '%(function)s(%(all_values)s%(expressions)s)'
    allow_distinct = False

    def __init__(self, expression, all_values=False, **extra):
        super().__init__(
            expression,
            all_values='ALL ' if all_values else '',
            **extra
        )

Value() выражения

class Value(value, output_field=None)

Объект Value() представляет собой самую маленькую возможную часть выражения: простое значение. Когда вам нужно представить значение целого числа, булевого значения или строки внутри выражения, можно обернуть это значение в Value().

Вам вряд ли понадобится использовать Value() напрямую. Когда вы пишете выражение F('field') + 1, Django неявно оборачивает 1 в Value(), позволяя простым значениям использоваться в более сложных выражениях. Вам нужно будет использовать Value() когда вы хотите передать строку в выражение. Большинство выражений интерпретируют строковый аргумент как имя поля, например Lower('name').

Аргумент value описывает значение, которое должно быть включено в выражение, например 1, True, или None.

Аргумент output_field должен быть экземпляром поля модели, например, IntegerField() или BooleanField(), в который Django загрузит значение после извлечения его из базы данных. Обычно при создании экземпляра поля модели не нужны никакие аргументы, так как любые аргументы, связанные с проверкой данных (max_length, max_digits, и т. д.), не будут применяться к значению выходных данных выражения. Если output_field не указан, он будет предположительно выведен из type предоставленного value, если это возможно. Например, передача экземпляра datetime.datetime как value по умолчанию установит output_field в DateTimeField.

ExpressionWrapper() выражения

class ExpressionWrapper(expression, output_field)

ExpressionWrapper окружает другое выражение и предоставляет доступ к свойствам, таким как output_field, которые могут быть недоступны в других выражениях. ExpressionWrapper необходимо при использовании арифметических операций над F() выражениями с разными типами, как описано в Использование F() с аннотациями.

Условные выражения

Условные выражения позволяют использовать логику if … elif … else в запросах. Django поддерживает выражения SQL CASE по умолчанию. Более подробная информация представлена в Условные выражения.

Subquery() выражения

class Subquery(queryset, output_field=None)

Вы можете добавить явную подзапрос к QuerySet используя выражение Subquery.

Например, чтобы аннотировать каждый пост адресом электронной почты автора самого нового комментария к этому посту:

>>> from django.db.models import OuterRef, Subquery
>>> newest = Comment.objects.filter(post=OuterRef('pk')).order_by('-created_at')
>>> Post.objects.annotate(newest_commenter_email=Subquery(newest.values('email')[:1]))

В PostgreSQL SQL выглядит так:

SELECT "post"."id", (
    SELECT U0."email"
    FROM "comment" U0
    WHERE U0."post_id" = ("post"."id")
    ORDER BY U0."created_at" DESC LIMIT 1
) AS "newest_commenter_email" FROM "post"

Ссылка на столбцы из внешнего набора запросов

class OuterRef(field)

Используйте OuterRef когда набор запросов в Subquery необходимо обратиться к полю из внешнего запроса или его преобразования. Он работает как выражение F, за исключением того, что проверка на то, относится ли он к действительному полю, не выполняется до тех пор, пока внешний набор запросов не будет разрешен.

Экземпляры OuterRef могут использоваться совместно с вложенными экземплярами Subquery для обращения к содержащему набору запросов, который не является непосредственным родителем. Например, этот набор запросов необходимо будет поместить в пару вложенных экземпляров Subquery для корректного разрешения:

>>> Book.objects.filter(author=OuterRef(OuterRef('pk')))

Ограничение подзапроса одним столбцом

Иногда необходимо вернуть один столбец из подзапроса, например, для использования подзапроса в качестве целевого объекта для поиска. Чтобы вернуть все комментарии для постов, опубликованных в течение последнего дня:

>>> from datetime import timedelta
>>> from django.utils import timezone
>>> one_day_ago = timezone.now() - timedelta(days=1)
>>> posts = Post.objects.filter(published_at__gte=one_day_ago)
>>> Comment.objects.filter(post__in=Subquery(posts.values('pk')))

В этом случае подзапрос должен использовать values() для возврата только одного столбца: первичного ключа поста.

Ограничение подзапроса одной строкой

Чтобы предотвратить возврат подзапросом нескольких строк, используется срез ([:1]) набора запросов:

>>> subquery = Subquery(newest.values('email')[:1])
>>> Post.objects.annotate(newest_commenter_email=subquery)

В этом случае подзапрос должен возвращать только один столбец и одну строку: адрес электронной почты самого последнего комментария.

(Использование get() вместо среза приведет к ошибке, потому что OuterRef не может быть разрешен до тех пор, пока набор запросов не будет использован внутри Subquery.)

Exists() подзапросы

class Exists(queryset)

Exists — это подкласс Subquery, который использует оператор SQL EXISTS . Во многих случаях он будет работать лучше, чем подзапрос, поскольку база данных может остановить оценку подзапроса при обнаружении первой совпадающей строки.

Например, чтобы аннотировать каждый пост тем, есть ли у него комментарий за последние сутки:

>>> from django.db.models import Exists, OuterRef
>>> from datetime import timedelta
>>> from django.utils import timezone
>>> one_day_ago = timezone.now() - timedelta(days=1)
>>> recent_comments = Comment.objects.filter(
...     post=OuterRef('pk'),
...     created_at__gte=one_day_ago,
... )
>>> Post.objects.annotate(recent_comment=Exists(recent_comments))

В PostgreSQL SQL выглядит так:

SELECT "post"."id", "post"."published_at", EXISTS(
    SELECT (1) as "a"
    FROM "comment" U0
    WHERE (
        U0."created_at" >= YYYY-MM-DD HH:MM:SS AND
        U0."post_id" = "post"."id"
    )
    LIMIT 1
) AS "recent_comment" FROM "post"

Не нужно заставлять Exists ссылаться на один столбец, поскольку столбцы игнорируются, и возвращается булево значение. Аналогично, так как порядок не важен в SQL EXISTS подзапросе и только ухудшает производительность, он автоматически удаляется.

Вы можете использовать NOT EXISTS с ~Exists().

Фильтрация по Subquery() или Exists() выражениям

Subquery() возвращающее булево значение, и Exists() могут использоваться в качестве condition в выражениях When или для непосредственной фильтрации набора запросов:

>>> recent_comments = Comment.objects.filter(...)  # From above
>>> Post.objects.filter(Exists(recent_comments))

Это гарантирует, что подзапрос не будет добавлен в SELECT столбцы, что может улучшить производительность.

Использование агрегатов в выражении Subquery

Агрегаты могут использоваться в Subquery, но для этого требуется специальная комбинация filter(), values() и annotate() для правильного группирования подзапроса.

Предполагая, что обе модели имеют поле length, чтобы найти посты, где длина поста больше, чем общая длина всех объединенных комментариев:

>>> from django.db.models import OuterRef, Subquery, Sum
>>> comments = Comment.objects.filter(post=OuterRef('pk')).order_by().values('post')
>>> total_comments = comments.annotate(total=Sum('length')).values('total')
>>> Post.objects.filter(length__gt=Subquery(total_comments))

Начальный filter(...) ограничивает подзапрос соответствующими параметрами. order_by() удаляет стандартный ordering (если есть) модели Comment. values('post') агрегирует комментарии по Post. Наконец, annotate(...) выполняет агрегацию. Порядок применения этих методов набора запросов важен. В данном случае, поскольку подзапрос должен быть ограничен одним столбцом, требуется values('total').

Это единственный способ выполнить агрегацию в Subquery, так как использование aggregate() пытается оценить набор запросов (и если существует OuterRef, это будет невозможно разрешить).

Выражения с необработанным SQL

class RawSQL(sql, params, output_field=None)

Иногда выражения базы данных не могут легко выразить сложное WHERE условие. В таких особых случаях используйте выражение RawSQL. Например:

>>> from django.db.models.expressions import RawSQL
>>> queryset.annotate(val=RawSQL("select col from sometable where othercol = %s", (param,)))

Эти дополнительные запросы могут быть непереносимы на другие движки баз данных (потому что вы явно пишете код SQL) и нарушают принцип DRY, поэтому их следует избегать, если это возможно.

Выражения RawSQL также могут использоваться в качестве целевого объекта для фильтров __in:

>>> queryset.filter(id__in=RawSQL("select id from sometable where col = %s", (param,)))

RawSQL("select col from sometable where othercol = '%s'")  # unsafe!

Функции окна

Функции окон позволяют применять функции к разделам. В отличие от обычной агрегационной функции, которая вычисляет конечный результат для каждого набора, определенного группировкой, функции окон работают с кадрами и разделами и вычисляют результат для каждой строки.

В одном запросе можно указать несколько окон, что в Django ORM эквивалентно включению нескольких выражений в вызов QuerySet.annotate(). ORM не использует именованные окна, вместо этого они являются частью выбранных столбцов.

class Window(expression, partition_by=None, order_by=None, frame=None, output_field=None)

filterable

По умолчанию False. Стандарт SQL запрещает ссылку на функции окон в WHERE-оператор и Django вызывает исключение при построении QuerySet , который это сделает.

template

По умолчанию %(expression)s OVER (%(window)s)'. Если предоставлен только аргумент expression, фраза окна будет пустой.

Класс Window — это основное выражение для OVER-оператор.

Аргумент expression — это функция окна, агрегационная функция или выражение, совместимое с фразой окна.

Аргумент partition_by принимает выражение или последовательность выражений (имена столбцов должны быть заключены в F-объект), которые управляют разделением строк. Разделение сужает строки, которые используются для вычисления набора результатов.

output_field задаётся либо в качестве аргумента, либо выражением.

Аргумент order_by принимает выражение или последовательность выражений, к которым можно обратиться к методам asc() и desc(). Сортировка определяет порядок применения выражения. Например, если вы суммируете строки в разделе, первый результат — значение первой строки, второй — сумма первой и второй строк.

Параметр frame определяет, какие другие строки следует использовать в вычислении. Подробности см. в разделе Кадры.

Например, чтобы добавить к каждой киноленте среднюю оценку для кинолент одного и того же студийного производства, того же жанра и года выпуска:

>>> from django.db.models import Avg, F, Window
>>> from django.db.models.functions import ExtractYear
>>> Movie.objects.annotate(
>>>     avg_rating=Window(
>>>         expression=Avg('rating'),
>>>         partition_by=[F('studio'), F('genre')],
>>>         order_by=ExtractYear('released').asc(),
>>>     ),
>>> )

Это позволяет проверить, лучше или хуже оценивается кинолента, чем её аналоги.

Вы можете применить несколько выражений к одному окну, т.е. одному разделу и одному кадру. Например, можно изменить предыдущий пример, чтобы он также включал лучшую и худшую оценку в каждой группе кинолент (одно и то же студийное производство, жанр и год выпуска), используя три функции окна в одном запросе. Раздел и порядок из предыдущего примера извлекаются в словарь для уменьшения повторений:

>>> from django.db.models import Avg, F, Max, Min, Window
>>> from django.db.models.functions import ExtractYear
>>> window = {
>>>    'partition_by': [F('studio'), F('genre')],
>>>    'order_by': ExtractYear('released').asc(),
>>> }
>>> Movie.objects.annotate(
>>>     avg_rating=Window(
>>>         expression=Avg('rating'), **window,
>>>     ),
>>>     best=Window(
>>>         expression=Max('rating'), **window,
>>>     ),
>>>     worst=Window(
>>>         expression=Min('rating'), **window,
>>>     ),
>>> )

Среди встроенных баз данных Django базы данных MySQL 8.0.2+, PostgreSQL и Oracle поддерживают выражения окон. Поддержка различных функций выражений окон различается в разных базах данных. Например, параметры в asc() и desc() могут не поддерживаться. При необходимости см. документацию вашей базы данных.

Кадры

Для кадра окна можно выбрать либо упорядоченную последовательность строк, либо обычную последовательность строк.

class ValueRange(start=None, end=None)

frame_type

Это свойство установлено в 'RANGE'.

PostgreSQL имеет ограниченную поддержку ValueRange и поддерживает только стандартные начальную и конечную точки, такие как CURRENT ROW и UNBOUNDED FOLLOWING.

class RowRange(start=None, end=None)

frame_type

Это свойство установлено в 'ROWS'.

Оба класса возвращают SQL с шаблоном:

%(frame_type)s BETWEEN %(start)s AND %(end)s

Кадры сужают строки, используемые для вычисления результата. Они сдвигаются от некоторой начальной точки к некоторой заданной конечной точке. Кадры могут использоваться с разделами и без них, но часто рекомендуется указать порядок окна для обеспечения детерминированного результата. В кадре элемент кадра — это строка с эквивалентным значением или все строки, если нет условия сортировки.

Начальная точка кадра по умолчанию — UNBOUNDED PRECEDING, которая является первой строкой раздела. Конечная точка всегда явно включается в SQL, генерируемый ORM, и по умолчанию UNBOUNDED FOLLOWING. Кадр по умолчанию включает все строки из раздела до последней строки в наборе.

Допустимые значения для аргументов start и end — None, целое число или ноль. Отрицательное целое число для start приводит к N preceding, а None приводит к UNBOUNDED PRECEDING. Для start и end ноль вернёт CURRENT ROW. Положительные целые числа допустимы для end.

Разница в том, что включает CURRENT ROW. Когда указано в режиме ROWS, кадр начинается или заканчивается текущей строкой. Когда указано в режиме RANGE, кадр начинается или заканчивается первой или последней строкой, соответствующей условию сортировки. Таким образом, RANGE CURRENT ROW оценивает выражение для строк, имеющих одинаковое значение, указанное в условии сортировки. Так как шаблон включает как start, так и end точки, это может быть выражено следующим образом:

ValueRange(start=0, end=0)

Если «аналоги» киноленты описываются как киноленты, выпущенные той же студией в том же жанре в том же году, этот RowRange пример добавляет к каждой киноленте среднюю оценку для двух предыдущих и двух последующих аналогичных кинолент:

>>> from django.db.models import Avg, F, RowRange, Window
>>> from django.db.models.functions import ExtractYear
>>> Movie.objects.annotate(
>>>     avg_rating=Window(
>>>         expression=Avg('rating'),
>>>         partition_by=[F('studio'), F('genre')],
>>>         order_by=ExtractYear('released').asc(),
>>>         frame=RowRange(start=-2, end=2),
>>>     ),
>>> )

Если база данных это поддерживает, вы можете указать начальную и конечную точки на основе значений выражения в разделе. Если поле released модели Movie хранит месяц выпуска каждой киноленты, этот ValueRange пример добавляет к каждой киноленте среднюю оценку кинолент, выпущенных за двенадцать месяцев до и за двенадцать месяцев после каждой киноленты.

>>> from django.db.models import Avg, F, ValueRange, Window
>>> Movie.objects.annotate(
>>>     avg_rating=Window(
>>>         expression=Avg('rating'),
>>>         partition_by=[F('studio'), F('genre')],
>>>         order_by=F('released').asc(),
>>>         frame=ValueRange(start=-12, end=12),
>>>     ),
>>> )

Технические сведения

Ниже приведены технические детали реализации, которые могут быть полезны разработчикам библиотек. Технический API и примеры ниже помогут при создании универсальных выражений запросов, которые могут расширить встроенную функциональность Django.

API выражений

Выражения запросов реализуют API выражений запросов, но также предоставляют ряд дополнительных методов и свойств, перечисленных ниже. Все выражения запросов должны наследоваться от Expression() или соответствующего подкласса.

Когда выражение запроса оборачивает другое выражение, оно должно вызывать соответствующие методы обернутого выражения.

class Expression

contains_aggregate

Указывает Django, что это выражение содержит агрегат и что к запросу необходимо добавить GROUP BY.

contains_over_clause

Указывает Django, что это выражение содержит выражение Window. Например, оно используется для запрета выражений оконных функций в запросах, изменяющих данные.

filterable

Указывает Django, что это выражение может быть использовано в QuerySet.filter(). По умолчанию True.

window_compatible

Указывает Django, что это выражение может быть использовано в качестве исходного выражения в Window. По умолчанию False.

empty_result_set_value

Новое в Django 4.0.

Указывает Django, какое значение должно быть возвращено, когда выражение используется для применения функции к пустому набору результатов. По умолчанию NotImplemented, что заставляет выражение вычисляться в базе данных.

resolve_expression(query=None, allow_joins=True, reuse=None, summarize=False, for_save=False)

Предоставляет возможность выполнить предварительную обработку или проверку выражения перед его добавлением в запрос. resolve_expression() также должен вызываться для всех вложенных выражений. Должно быть возвращено copy() с self необходимыми преобразованиями.

query — реализация запроса на стороне базы данных.

allow_joins — булево значение, которое разрешает или запрещает использование соединений в запросе.

reuse — набор переиспользуемых соединений для многосоединительных сценариев.

summarize — булево значение, которое, когда True, сигнализирует о том, что вычисляемый запрос является конечным агрегатным запросом.

for_save — булево значение, которое, когда True, сигнализирует о том, что выполняемый запрос выполняет создание или обновление.

get_source_expressions()

Возвращает упорядоченный список внутренних выражений. Например:

>>> Sum(F('foo')).get_source_expressions()
[F('foo')]

set_source_expressions(expressions)

Принимает список выражений и сохраняет их таким образом, чтобы get_source_expressions() мог их вернуть.

relabeled_clone(change_map)

Возвращает клон (копию) self, с переименованием любых псевдонимов столбцов. Псевдонимы столбцов переименовываются при создании подзапросов. relabeled_clone() также должен вызываться для всех вложенных выражений и присваиваться клону.

change_map — словарь, сопоставляющий старые псевдонимы новым.

Пример:

def relabeled_clone(self, change_map):
    clone = copy.copy(self)
    clone.expression = self.expression.relabeled_clone(change_map)
    return clone

convert_value(value, expression, connection)

Обратный вызов, позволяющий выражению привести value к более подходящему типу.

expression эквивалентно self.

get_group_by_cols(alias=None)

Ответственен за возвращение списка столбцов, на которые ссылается это выражение. get_group_by_cols() должен вызываться для всех вложенных выражений. Объекты F(), в частности, хранят ссылку на столбец. Параметр alias будет None, если выражение не было аннотировано и используется для группировки.

asc(nulls_first=False, nulls_last=False)

Возвращает выражение, готовое для сортировки по возрастанию.

nulls_first и nulls_last определяют, как сортируются нулевые значения. См. Использование F() для сортировки нулевых значений для примера использования.

desc(nulls_first=False, nulls_last=False)

Возвращает выражение, готовое для сортировки по убыванию.

nulls_first и nulls_last определяют, как сортируются нулевые значения. См. Использование F() для сортировки нулевых значений для примера использования.

reverse_ordering()

Возвращает self с любыми необходимыми изменениями для изменения порядка сортировки в вызове order_by. Например, выражение, реализующее NULLS LAST, изменит своё значение на NULLS FIRST. Изменения требуются только для выражений, реализующих порядок сортировки, таких как OrderBy. Этот метод вызывается при вызове reverse() на наборе запросов.

Написание собственных выражений запросов

Вы можете написать собственные классы выражений запросов, которые используют и могут интегрироваться с другими выражениями запросов. Давайте рассмотрим пример, написав реализацию SQL-функции COALESCE, не используя встроенные Выражения Func().

SQL-функция COALESCE определена как принимающая список столбцов или значений. Она вернёт первый столбец или значение, которое не NULL.

Начнём с определения шаблона, используемого для генерации SQL, и метода __init__() для установки некоторых атрибутов:

import copy
from django.db.models import Expression

class Coalesce(Expression):
    template = 'COALESCE( %(expressions)s )'

    def __init__(self, expressions, output_field):
      super().__init__(output_field=output_field)
      if len(expressions) < 2:
          raise ValueError('expressions must have at least 2 elements')
      for expression in expressions:
          if not hasattr(expression, 'resolve_expression'):
              raise TypeError('%r is not an Expression' % expression)
      self.expressions = expressions

Мы выполняем базовую проверку параметров, включая требование как минимум 2 столбцов или значений и обеспечение того, что они являются выражениями. Мы требуем output_field здесь, чтобы Django знал, к какому типу поля модели назначить конечный результат.

Теперь мы реализуем предварительную обработку и проверку. Поскольку у нас нет собственной проверки на данном этапе, мы делегируем это вложенным выражениям:

def resolve_expression(self, query=None, allow_joins=True, reuse=None, summarize=False, for_save=False):
    c = self.copy()
    c.is_summary = summarize
    for pos, expression in enumerate(self.expressions):
        c.expressions[pos] = expression.resolve_expression(query, allow_joins, reuse, summarize, for_save)
    return c

Далее, мы пишем метод, ответственный за генерацию SQL:

def as_sql(self, compiler, connection, template=None):
    sql_expressions, sql_params = [], []
    for expression in self.expressions:
        sql, params = compiler.compile(expression)
        sql_expressions.append(sql)
        sql_params.extend(params)
    template = template or self.template
    data = {'expressions': ','.join(sql_expressions)}
    return template % data, sql_params

def as_oracle(self, compiler, connection):
    """
    Example of vendor specific handling (Oracle in this case).
    Let's make the function name lowercase.
    """
    return self.as_sql(compiler, connection, template='coalesce( %(expressions)s )')

Методы as_sql() могут поддерживать пользовательские ключевые аргументы, позволяя методам as_vendorname() переопределять данные, используемые для генерации SQL-строки. Использование ключевых аргументов as_sql() для настройки предпочтительнее, чем изменение self в методах as_vendorname(), поскольку последнее может приводить к ошибкам при работе на разных базах данных. Если ваш класс полагается на атрибуты класса для определения данных, рассмотрите возможность разрешения переопределений в вашем методе as_sql().

Мы генерируем SQL для каждого из expressions с помощью метода compiler.compile(), и объединяем результат запятыми. Затем шаблон заполняется нашими данными, и возвращаются SQL и параметры.

Мы также определили пользовательскую реализацию, специфичную для бэкенда Oracle. Функция as_oracle() будет вызвана вместо as_sql() при использовании бэкенда Oracle.

Наконец, мы реализуем остальные методы, которые позволяют нашему выражению запроса хорошо работать с другими выражениями запросов:

def get_source_expressions(self):
    return self.expressions

def set_source_expressions(self, expressions):
    self.expressions = expressions

Давайте посмотрим, как это работает:

>>> from django.db.models import F, Value, CharField
>>> qs = Company.objects.annotate(
...    tagline=Coalesce([
...        F('motto'),
...        F('ticker_name'),
...        F('description'),
...        Value('No Tagline')
...        ], output_field=CharField()))
>>> for c in qs:
...     print("%s: %s" % (c.name, c.tagline))
...
Google: Do No Evil
Apple: AAPL
Yahoo: Internet Company
Django Software Foundation: No Tagline

Избегание SQL-инъекций

Поскольку ключевые аргументы Func для __init__() (**extra) и as_sql() (**extra_context) интерполируются в SQL-строку, а не передаются в качестве параметров запроса (где драйвер базы данных их экранирует), они не должны содержать недоверенные данные пользователя.

Например, если substring — данные пользователя, эта функция уязвима к SQL-инъекциям:

from django.db.models import Func

class Position(Func):
    function = 'POSITION'
    template = "%(function)s('%(substring)s' in %(expressions)s)"

    def __init__(self, expression, substring):
        # substring=substring is an SQL injection vulnerability!
        super().__init__(expression, substring=substring)

Эта функция генерирует SQL-строку без каких-либо параметров. Так как substring передаётся super().__init__() как ключевой аргумент, она интерполируется в SQL-строку до отправки запроса в базу данных.

Вот исправленная переработка:

class Position(Func):
    function = 'POSITION'
    arg_joiner = ' IN '

    def __init__(self, expression, substring):
        super().__init__(substring, expression)

При передаче substring в качестве позиционного аргумента, он будет передан как параметр в запросе к базе данных.

Добавление поддержки в сторонних бэкендах баз данных

Если вы используете бэкенд базы данных, который использует другой синтаксис SQL для определённой функции, вы можете добавить поддержку, подменяя новым методом функцию в классе.

Предположим, что мы пишем бэкенд для SQL Server от Microsoft, который использует SQL LEN вместо LENGTH для функции Length. Мы подменяем новым методом, называемым as_sqlserver() в класс Length:

from django.db.models.functions import Length

def sqlserver_length(self, compiler, connection):
    return self.as_sql(compiler, connection, function='LEN')

Length.as_sqlserver = sqlserver_length

Вы также можете настроить SQL с использованием параметра template метода as_sql().

Мы используем as_sqlserver() , так как django.db.connection.vendor возвращает sqlserver для бэкенда.

Сторонние бэкенды могут регистрировать свои функции в файле верхнего уровня __init__.py пакета бэкенда или в файле (или пакете) верхнего уровня expressions.py, который импортируется из верхнего уровня __init__.py.

Для пользовательских проектов, желающих подменить используемый ими бэкенд, этот код должен находиться в методе AppConfig.ready().