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

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

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

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

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 lookups. Чтобы вернуть все комментарии к постам, опубликованным в течение последнего дня:

>>> 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 U0."id", U0."post_id", U0."email", U0."created_at"
    FROM "comment" U0
    WHERE (
        U0."created_at" >= YYYY-MM-DD HH:MM:SS AND
        U0."post_id" = ("post"."id")
    )
) 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", (someparam,)))

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

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

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

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

Добавлен параметр alias.

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() на наборе запросов.

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

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