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

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

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

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

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

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

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

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

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

class F

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

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

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

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

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

from django.db.models import F

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

from django.db.models import F, Func

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

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

class Lower(Func):
    function = 'LOWER'

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

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

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

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

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

class Func(*expressions, **extra)

function

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

template

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

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

arg_joiner

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

arity

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

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

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

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

django/db/models/functions.py

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

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

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

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

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

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

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

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

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

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

from django.db.models import Count

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

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

class Aggregate(*expressions, output_field=None, distinct=False, filter=None, **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 передано.

Позиционные аргументы 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, который используется для фильтрации строк, которые агрегируются. См. Условную агрегацию и Фильтрование аннотаций для примера использования.

Ключевые аргументы **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')))

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

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

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

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

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

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

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

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

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

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

class Exists(queryset)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Необработанные выражения SQL

class RawSQL(sql, params, output_field=None)

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

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

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

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

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

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

Функции окон

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

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

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

filterable

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

template

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

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

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

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

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

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

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

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

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

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

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

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

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

Кадры

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

class ValueRange(start=None, end=None)

frame_type

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

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

class RowRange(start=None, end=None)

frame_type

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

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

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

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

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

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

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

ValueRange(start=0, end=0)

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

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

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

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

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

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

API выражений

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

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

class Expression

contains_aggregate

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

contains_over_clause

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

filterable

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

window_compatible

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

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

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

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

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

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

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

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

get_source_expressions()

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

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

set_source_expressions(expressions)

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

relabeled_clone(change_map)

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

change_map — словарь, отображающий старые алиасы на новые.

Пример:

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

convert_value(value, expression, connection)

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

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

get_group_by_cols(alias=None)

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

asc(nulls_first=False, nulls_last=False)

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

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

desc(nulls_first=False, nulls_last=False)

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

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

reverse_ordering()

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

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

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

Функция COALESCE SQL определяется как принимающая список столбцов или значений. Она вернёт первый столбец или значение, которое не 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().