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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

class F

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

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

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

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

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

from django.db.models import F

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

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

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

Какое значение было или есть у reporter.stories_filed, Python никогда не узнает об этом - с ним полностью работает база данных. Всё, что делает Python через класс F() Django, это создаёт 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() не поддерживает 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))

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

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

from django.db.models import F, Func

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

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

class Lower(Func):
    function = 'LOWER'

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

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

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

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

API Func следующий:

class Func(*expressions, **extra)

function

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

template

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

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

arg_joiner

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

arity

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

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

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

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

django/db/models/functions.py

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

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

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

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

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

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

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

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

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

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

from django.db.models import Count

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

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

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

template

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

function

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

window_compatible

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

allow_distinct

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

empty_result_set_value

Новое в Django 4.0.

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

filterable

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

template

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

Класс Window является основным выражением для OVER-клаузы.

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

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

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

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

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

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

>>> from django.db.models import Avg, F, Window
>>> 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,
>>>     ),
>>> )

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

contains_aggregate

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

contains_over_clause

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

filterable

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

window_compatible

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

empty_result_set_value

Новое в Django 4.0.

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

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

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

query — реализация запроса бэкендом.

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

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

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

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

get_source_expressions()

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

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

set_source_expressions(expressions)

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

relabeled_clone(change_map)

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

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

Пример:

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

convert_value(value, expression, connection)

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

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

get_group_by_cols(alias=None)

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

asc(nulls_first=None, nulls_last=None)

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

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

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

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

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

from django.db.models.functions import Length

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

Length.as_sqlserver = sqlserver_length

Вы также можете настроить SQL, используя параметр template функции as_sql().

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

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

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