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

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

Поддерживаемая арифметика

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

Поле вывода

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

output_field принимает экземпляр поля модели, например, IntegerField() или BooleanField(). Обычно поле не требует никаких аргументов, например, max_length, так как аргументы поля относятся к валидации данных, которая не будет выполняться для значения, вычисленного выражением.

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

Примеры

>>> 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, OuterRef
>>> 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.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() не поддерживает, вам необходимо обернуть выражение в 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))

Использование F() с логическими операциями

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

from django.db.models import F

Company.objects.update(is_active=~F("is_active"))

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

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

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

Аргумент 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 . Django знает, как преобразовать эти значения Python в соответствующий тип базы данных.

Если output_field не указан, он будет выведен из типа предоставленного 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")))

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

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

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

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
>>> Movie.objects.annotate(
...     avg_rating=Window(
...         expression=Avg("rating"),
...         partition_by=[F("studio"), F("genre")],
...         order_by="released__year",
...     ),
... )

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

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

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

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

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

>>> qs = Movie.objects.annotate(
...     category_rank=Window(Rank(), partition_by="category", order_by="-rating"),
...     scenes_count=Count("actors"),
... ).filter(Q(category_rank__lte=3) | Q(title__contains="Batman"))
>>> list(qs)
NotImplementedError: Heterogeneous disjunctive predicates against window functions
are not implemented when performing conditional aggregation.

Среди встроенных баз данных Django, MySQL, 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
>>> Movie.objects.annotate(
...     avg_rating=Window(
...         expression=Avg("rating"),
...         partition_by=[F("studio"), F("genre")],
...         order_by="released__year",
...         frame=RowRange(start=-2, end=2),
...     ),
... )

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

>>> 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="released__year",
...         frame=ValueRange(start=-12, end=12),
...     ),
... )

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

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

API выражений

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

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

class Expression

allowed_default

Новое в Django 5.0.

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

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, какое значение должно возвращаться, когда выражение используется для применения функции над пустым набором результатов. По умолчанию 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()

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

Изменено в Django 4.2:

Ключевое слово alias=None было удалено.

asc(nulls_first=None, nulls_last=None)

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

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

desc(nulls_first=None, nulls_last=None)

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

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

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 для определённой функции, вы можете добавить поддержку, монтируя новый метод в класс функции.

Предположим, что мы пишем бэкенд для Microsoft SQL Server, который использует 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().