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

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

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

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
>>> 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")),
... )

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

Примечание

Эти выражения определены в django.db.models.expressions и django.db.models.aggregates, но для удобства они доступны и обычно импортируются из django.db.models.

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.

Это документировано в using F() expressions in queries.

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

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

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

Если объединяемые поля имеют разные типы, то необходимо указать Django, какой тип поля будет возвращен. Большинство выражений поддерживают output_field для этого случая, но поскольку 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 - это ноль), после компаний, с которыми связывались:

from django.db.models import F

Company.objects.order_by(F("last_contacted").desc(nulls_last=True))

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

New in Django 4.2.

<<<Выражения 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 must not contain untrusted user input, поскольку эти значения интерполируются в строку SQL, а не передаются как параметры запроса, где драйвер базы данных мог бы избежать их.

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

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

Кармашки **extra представляют собой пары key=value, которые могут быть интерполированы в атрибут template. Чтобы избежать уязвимости SQL-инъекции, extra must not contain untrusted user input, поскольку эти значения интерполируются в строку SQL, а не передаются как параметры запроса, где драйвер базы данных будет их избегать.

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

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

Агрегатное выражение - это частный случай выражения Func() expression, которое сообщает запросу, что требуется предложение GROUP BY. Все aggregate functions, как и Sum() и Count(), наследуются от Aggregate().

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

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() с аннотациями.

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

Условные выражения позволяют использовать ifelifelse логику в запросах. 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"

Примечание

Примеры в этом разделе призваны показать, как заставить Django выполнить подзапрос. В некоторых случаях можно написать эквивалентный кверисет, который выполняет ту же задачу более четко или эффективно.

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

class OuterRef(field)[исходный код]

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

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

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

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

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

Предупреждение

Чтобы защититься от SQL injection attacks, вы должны исключить любые параметры, которыми может управлять пользователь, используя params. params является обязательным аргументом, чтобы заставить вас признать, что вы не интерполируете ваш SQL с данными, предоставленными пользователем.

Вы также не должны заключать заполнители в кавычки в строке SQL. Этот пример уязвим для внедрения SQL из-за кавычек вокруг %s:

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

Вы можете прочитать больше о том, как работает SQL injection protection в Django.

Функции окна

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

Вы можете указать несколько окон в одном запросе, что в 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 является либо window function, либо aggregate function, либо выражением, совместимым в клаузе окна.

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

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

Аргумент order_by принимает выражение, на которое можно вызвать asc() и desc(), строку имени поля (с необязательным префиксом "-", который указывает на убывающий порядок), или кортеж или список строк и/или выражений. Упорядочение определяет порядок применения выражения. Например, если вы суммируете строки в разделе, то первым результатом будет значение первой строки, а вторым - сумма первой и второй строк.

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

Changed in Django 4.1:

Добавлена поддержка ссылок order_by по имени поля.

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

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

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

>>> 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.
Changed in Django 4.2:

Добавлена поддержка фильтрации по оконным функциям.

Среди встроенных в 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 выражения

Выражения запроса реализуют query expression 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() объекты, в частности, хранят ссылку на столбец.

Changed in Django 4.2:

Удален аргумент с ключевым словом alias=None.

asc(nulls_first=None, nulls_last=None)

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

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

Changed in 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 определяют, как сортируются нулевые значения. Пример использования см. в Использование F() для сортировки нулевых значений.

Changed in 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() expressions.

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

Вернуться на верх