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

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

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

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

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

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

class F [source]

Объект 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() для сортировки NULL значений

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

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

from django.db.models import F
Company.object.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"

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

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

class Func(*expressions, **extra) [source]

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) [source]

Генерирует SQL-запрос для функции базы данных.

Методы 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) [source]

template

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

function

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

window_compatible

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

allow_distinct

Добавлено в Django 2.2.

Атрибут класса, определяющий, разрешено ли использование ключевого аргумента 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) [source]

Объект 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) [source]

ExpressionWrapper просто окружает другое выражение и предоставляет доступ к свойствам, таким как output_field, которые могут быть недоступны для других выражений. ExpressionWrapper необходимо при использовании арифметических операций с F() выражениями разных типов, как описано в Использование F() с аннотациями.

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

Условные выражения позволяют использовать логику if … elif … else в запросах. Django напрямую поддерживает SQL CASE выражения. Более подробную информацию см. в Условных выражениях.

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

class Subquery(queryset, output_field=None) [source]

Вы можете добавить явное подзапрос к 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) [source]

Используйте 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) [source]

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 выражению

Невозможно выполнить фильтрацию непосредственно с помощью Subquery и Exists, например:

>>> Post.objects.filter(Exists(recent_comments))
...
TypeError: 'Exists' object is not iterable

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

>>> Post.objects.annotate(
...     recent_comment=Exists(recent_comments),
... ).filter(recent_comment=True)

Использование агрегаций внутри 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) [source]

Иногда сложно выразить сложный 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!

Окна функций

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

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

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

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) [source]

frame_type

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

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

class RowRange(start=None, end=None) [source]

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 [source]

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 к более подходящему типу.

get_group_by_cols()

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

asc(nulls_first=False, nulls_last=False)

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

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

desc(nulls_first=False, nulls_last=False)

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

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

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