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

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

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

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

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

Примечание

Эти выражения определены в django.db.models.expressions и django.db.models.aggregates, но для удобства они доступны и обычно импортируются из django.db.models.

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

class F[исходный код]

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

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

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

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

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

from django.db.models import F

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

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

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

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

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

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

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

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

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

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

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

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

Избегание условий гонки с помощью F()

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

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

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

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

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

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

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

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

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

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

Это документировано в using F() expressions in queries.

Использование 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 - это ноль), после компаний, с которыми связывались:

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 must not contain untrusted user input, так как эти значения интерполируются в строку SQL, а не передаются как параметры запроса, где драйвер базы данных мог бы избежать их.

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

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

Значения **extra kwargs представляют собой пары key=value, которые могут быть интерполированы в атрибут template. Чтобы избежать уязвимости SQL-инъекции, extra must not contain untrusted user input, поскольку эти значения интерполируются в строку SQL, а не передаются как параметры запроса, где драйвер базы данных мог бы их избежать.

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

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

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

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

from django.db.models import Count

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

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

class Aggregate(*expressions, output_field=None, distinct=False, filter=None, **extra)[исходный код]
template

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

function

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

window_compatible

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

allow_distinct

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

Позиционные аргументы expressions могут включать выражения или имена полей модели. Они будут преобразованы в строку и использованы в качестве expressions заполнителя в template.

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

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

Аргумент distinct определяет, следует ли вызывать агрегатную функцию для каждого отдельного значения expressions (или набора значений, для множества expressions). Этот аргумент поддерживается только для агрегатов, у которых allow_distinct установлено значение True.

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

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

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

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

from django.db.models import Aggregate

class Sum(Aggregate):
    # Supports SUM(ALL field).
    function = 'SUM'
    template = '%(function)s(%(all_values)s%(expressions)s)'
    allow_distinct = False

    def __init__(self, expression, all_values=False, **extra):
        super().__init__(
            expression,
            all_values='ALL ' if all_values else '',
            **extra
        )

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

class Value(value, output_field=None)[исходный код]

Объект Value() представляет наименьший возможный компонент выражения: простое значение. Когда вам нужно представить значение целого числа, булевой или строки в выражении, вы можете обернуть это значение в объект Value().

Вам редко придется использовать Value() напрямую. Когда вы пишете выражение F('field') + 1, Django неявно оборачивает 1 в Value(), позволяя использовать простые значения в более сложных выражениях. Вам придется использовать Value(), когда вы хотите передать строку в выражение. Большинство выражений интерпретируют строковый аргумент как имя поля, например Lower('name').

Аргумент value описывает значение, которое должно быть включено в выражение, например 1, True или None. Django знает, как преобразовать эти значения Python в соответствующий им тип базы данных.

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

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

class ExpressionWrapper(expression, output_field)[исходный код]

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

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

Условные выражения позволяют использовать ifelifelse логику в запросах. 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"

Примечание

Примеры в этом разделе призваны показать, как заставить Django выполнить подзапрос. В некоторых случаях можно написать эквивалентный кверисет, который выполняет ту же задачу более четко или эффективно.

Ссылка на столбцы из внешнего набора queryset

class OuterRef(field)[исходный код]

Используйте OuterRef, когда кверисет в Subquery должен ссылаться на поле из внешнего запроса. Он действует как выражение F, за исключением того, что проверка, ссылается ли он на действительное поле, не производится до тех пор, пока не будет разрешен внешний кверисет.

Экземпляры OuterRef могут использоваться вместе с вложенными экземплярами Subquery для ссылки на содержащий кверисет, который не является непосредственным родителем. Например, этот кверисет должен находиться внутри вложенной пары экземпляров Subquery для правильного разрешения:

>>> Book.objects.filter(author=OuterRef(OuterRef('pk')))

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

Бывают случаи, когда из Subquery необходимо вернуть один столбец, например, чтобы использовать Subquery в качестве цели поиска __in. Чтобы вернуть все комментарии к сообщениям, опубликованным за последний день:

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

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

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

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

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

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

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

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

class Exists(queryset)[исходный код]

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

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

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

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

SELECT "post"."id", "post"."published_at", EXISTS(
    SELECT 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, что может привести к улучшению производительности.

Changed in Django 3.0:

В предыдущих версиях Django необходимо было сначала аннотировать, а затем фильтровать по аннотации. Это приводило к тому, что аннотированное значение всегда присутствовало в результате запроса, и часто запрос занимал больше времени на выполнение.

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

Предупреждение

Чтобы защититься от SQL injection attacks, вы должны исключить любые параметры, которыми может управлять пользователь, используя params. params является обязательным аргументом, чтобы заставить вас признать, что вы не интерполируете ваш SQL с данными, предоставленными пользователем.

Вы также не должны заключать в кавычки в строке SQL. Этот пример уязвим для внедрения SQL из-за кавычек вокруг %s:

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

Вы можете прочитать больше о том, как работает в Django Защита от SQL-инъекций.

Функции окна

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

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

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

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

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

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

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

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

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

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

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

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

Рамки

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

class ValueRange(start=None, end=None)[исходный код]
frame_type

Этот атрибут имеет значение 'RANGE'.

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

class RowRange(start=None, end=None)[исходный код]
frame_type

Этот атрибут имеет значение 'ROWS'.

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

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

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

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

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

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

ValueRange(start=0, end=0)

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

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

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

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

Техническая информация

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

API выражения

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

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

class Expression[исходный код]
contains_aggregate

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

contains_over_clause

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

filterable

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

window_compatible

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

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

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

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

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

reuse - это набор многократно используемых джоинов для сценариев с несколькими джоинами.

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

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

get_source_expressions()

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

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

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

relabeled_clone(change_map)

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

change_map - это словарь, отображающий старые псевдонимы на новые псевдонимы.

Пример:

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

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

expression это то же самое, что и self.

get_group_by_cols(alias=None)

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

Changed in Django 3.0:

Был добавлен параметр alias.

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

SQL-функция COALESCE определяется как прием списка столбцов или значений. Она возвращает первый столбец или значение, которое не является NULL.

Мы начнем с определения шаблона, который будет использоваться для генерации SQL, и метода __init__() для установки некоторых атрибутов:

import copy
from django.db.models import Expression

class Coalesce(Expression):
    template = 'COALESCE( %(expressions)s )'

    def __init__(self, expressions, output_field):
      super().__init__(output_field=output_field)
      if len(expressions) < 2:
          raise ValueError('expressions must have at least 2 elements')
      for expression in expressions:
          if not hasattr(expression, 'resolve_expression'):
              raise TypeError('%r is not an Expression' % expression)
      self.expressions = expressions

Мы выполняем некоторую базовую проверку параметров, включая требование наличия как минимум 2 столбцов или значений, а также убеждаемся, что они являются выражениями. Здесь мы требуем output_field, чтобы Django знал, к какому полю модели отнести конечный результат.

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

def resolve_expression(self, query=None, allow_joins=True, reuse=None, summarize=False, for_save=False):
    c = self.copy()
    c.is_summary = summarize
    for pos, expression in enumerate(self.expressions):
        c.expressions[pos] = expression.resolve_expression(query, allow_joins, reuse, summarize, for_save)
    return c

Далее мы напишем метод, отвечающий за генерацию SQL:

def as_sql(self, compiler, connection, template=None):
    sql_expressions, sql_params = [], []
    for expression in self.expressions:
        sql, params = compiler.compile(expression)
        sql_expressions.append(sql)
        sql_params.extend(params)
    template = template or self.template
    data = {'expressions': ','.join(sql_expressions)}
    return template % data, sql_params

def as_oracle(self, compiler, connection):
    """
    Example of vendor specific handling (Oracle in this case).
    Let's make the function name lowercase.
    """
    return self.as_sql(compiler, connection, template='coalesce( %(expressions)s )')

Методы as_sql() могут поддерживать пользовательские аргументы ключевых слов, что позволяет методам as_vendorname() переопределять данные, используемые для генерации строки SQL. Использование as_sql() аргументов ключевых слов для настройки предпочтительнее, чем мутирование self внутри as_vendorname() методов, так как последнее может привести к ошибкам при работе на различных бэкендах баз данных. Если ваш класс полагается на атрибуты класса для определения данных, подумайте о возможности переопределения в методе as_sql().

Мы генерируем SQL для каждого из expressions с помощью метода compiler.compile() и соединяем результат запятыми. Затем шаблон заполняется нашими данными и возвращаются SQL и параметры.

Мы также определили пользовательскую реализацию, специфичную для бэкенда Oracle. Функция as_oracle() будет вызвана вместо as_sql(), если используется бэкенд Oracle.

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

def get_source_expressions(self):
    return self.expressions

def set_source_expressions(self, expressions):
    self.expressions = expressions

Давайте посмотрим, как это работает:

>>> from django.db.models import F, Value, CharField
>>> qs = Company.objects.annotate(
...    tagline=Coalesce([
...        F('motto'),
...        F('ticker_name'),
...        F('description'),
...        Value('No Tagline')
...        ], output_field=CharField()))
>>> for c in qs:
...     print("%s: %s" % (c.name, c.tagline))
...
Google: Do No Evil
Apple: AAPL
Yahoo: Internet Company
Django Software Foundation: No Tagline

Избегание SQL-инъекций

Поскольку аргументы ключевого слова Func для __init__() (**extra) и as_sql() (**extra_context) интерполируются в строку SQL, а не передаются как параметры запроса (где драйвер базы данных мог бы их избежать), они не должны содержать недоверенный пользовательский ввод.

Например, если substring предоставляется пользователем, эта функция уязвима для SQL-инъекции:

from django.db.models import Func

class Position(Func):
    function = 'POSITION'
    template = "%(function)s('%(substring)s' in %(expressions)s)"

    def __init__(self, expression, substring):
        # substring=substring is an SQL injection vulnerability!
        super().__init__(expression, substring=substring)

Эта функция генерирует строку SQL без каких-либо параметров. Поскольку substring передается в super().__init__() в качестве аргумента ключевого слова, оно интерполируется в строку SQL перед отправкой запроса в базу данных.

Вот исправленный рерайт:

class Position(Func):
    function = 'POSITION'
    arg_joiner = ' IN '

    def __init__(self, expression, substring):
        super().__init__(substring, expression)

Если вместо этого передать substring в качестве позиционного аргумента, то он будет передан в качестве параметра в запросе к базе данных.

Добавление поддержки в бэкендах баз данных сторонних производителей

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

Допустим, мы пишем бэкэнд для 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().

Вернуться на верх