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

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

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

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 выглядит как обычная операция присваивания значения атрибуту экземпляра, на самом деле это 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(). Это позволяет уменьшить количество запросов с двух (как в первом примере) — запрос для получения объекта и 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() не напрямую поддерживает 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() для сортировки значений NULL

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

Например, для сортировки компаний, с которыми не связывались (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.

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

Аргумент 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")))

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

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

Использование агрегаций в выражении подзапроса

Агрегации могут использоваться в подзапросе, но требуют определённой комбинации 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') необходимо.

Это единственный способ выполнить агрегацию в подзапросе, так как использование 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 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
>>> 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 пример аннотирует каждый фильм средней оценкой коллег, выпущенных в период между двенадцатью месяцами до и двенадцатью месяцами после каждого фильма:

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

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 для примера использования.

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

В более ранних версиях nulls_first и nulls_last по умолчанию были False.

Устарело начиная с версии 4.1: Передача nulls_first=False или nulls_last=False в asc() устарела. Используйте None вместо этого.

desc(nulls_first=None, nulls_last=None)

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

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

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

В более ранних версиях nulls_first и nulls_last по умолчанию были False.

Устарело начиная с версии 4.1: Передача nulls_first=False или nulls_last=False в desc() устарела. Используйте None вместо этого.

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