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

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

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

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()
Company.objects.order_by(Length('name').asc())
Company.objects.order_by(Length('name').desc())

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

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.

Использование 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.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"

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

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

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 по мере необходимости. Например:

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

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

Чтобы избежать уязвимости 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(expression, output_field=None, filter=None, **extra) [source]

template

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

function

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

window_compatible

Новое в Django 2.0.

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

Аргумент expression может быть именем поля в модели или другим выражением. Он будет преобразован в строку и использован как expressions замещение внутри template.

Аргумент output_field требует экземпляра поля модели, например, IntegerField() или BooleanField(), в который Django загрузит значение после извлечения из базы данных. Обычно при создании экземпляра поля модели не нужны аргументы, так как любые аргументы, относящиеся к проверке данных (max_length, max_digits, и т.д.), не будут применяться к выходному значению выражения.

Обратите внимание, что output_field требуется только тогда, когда Django не может определить тип поля результата. Сложные выражения, которые смешивают типы полей, должны определить желаемый output_field. Например, сложение IntegerField() и FloatField() должно иметь определённый output_field=FloatField().

Аргумент filter принимает Q object, который используется для фильтрации строк, которые агрегируются. См. Условную агрегацию и Фильтрацию по аннотациям для примеров использования.

Ключевые слова **extra — это key=value пары, которые могут быть интерполированы в template атрибут.

Создание собственных агрегатных функций

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

from django.db.models import Aggregate

class Count(Aggregate):
    # supports COUNT(distinct field)
    function = 'COUNT'
    template = '%(function)s(%(distinct)s%(expressions)s)'

    def __init__(self, expression, distinct=False, **extra):
        super().__init__(
            expression,
            distinct='DISTINCT ' if distinct else '',
            output_field=IntegerField(),
            **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 поиска. Чтобы вернуть все комментарии для постов, опубликованных в течение последнего дня:

>>> 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 и 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, но они требуют определенной комбинации 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!

Функции окон

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

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

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

filterable

Новое в Django 2.0.

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

window_compatible

Новое в Django 2.0.

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

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

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

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

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

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

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

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

Были добавлены параметры nulls_last и nulls_first.

desc(nulls_first=False, nulls_last=False)

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

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

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

Были добавлены параметры nulls_first и nulls_last.

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

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

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