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

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

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

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

класс 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()

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

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

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

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

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

Помимо использования в операциях с отдельными экземплярами, как указано выше, F()его можно использовать QuerySetsдля экземпляров объектов с помощью update(). Это сокращает два запроса, которые мы использовали выше - the get()и the 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 для работы
  • уменьшение количества запросов, требуемых некоторыми операциями
Изменено в Django 3.2:

Добавлена ​​поддержка преобразований поля.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

from django.db.models import F, Func

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

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

class Lower(Func):
    function = 'LOWER'

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

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

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

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

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

классFunc ( * выражения , ** дополнительные )
function

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

template

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

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

arg_joiner

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

arity

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

as_sql( компилятор , соединение , функция = Нет , шаблон = Нет , arg_joiner = Нет , ** extra_context )

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

Эти 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()выражения.

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

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

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

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

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

from django.db.models import Count

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

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

классаAggregate ( * выражения , output_field = None , отличный = False , фильтр = None , ** за дополнительную плату )
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

В **extrakwargs являются key=valueпарами , которые могут быть интерполированы в templateатрибут.

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

Добавлена ​​поддержка преобразований поля.

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

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

classValue ( значение , output_field = None )

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

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

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

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

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

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

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

классExpressionWrapper ( выражение , output_field )

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

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

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

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

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

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

классOuterRef ( поле )

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

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

>>> Book.objects.filter(author=OuterRef(OuterRef('pk')))
Изменено в Django 3.2:

Добавлена ​​поддержка преобразований поля.

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

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

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

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

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

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

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

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

классExists ( набор запросов )

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

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

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

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

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

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

Вы можете запросить, используя with .NOT EXISTS~Exists()

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

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

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

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

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

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

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

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

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

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

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

классRawSQL ( sql , params , output_field = None )

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

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

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

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

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

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

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

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

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

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

Оконные функции

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

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

classWindow ( выражение , partition_by = None , order_by = None , frame = None , output_field = None )
filterable

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

template

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

Рамки

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

classValueRange ( начало = Нет , конец = Нет )
frame_type

Для этого атрибута установлено значение 'RANGE'.

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

classRowRange ( начало = Нет , конец = Нет )
frame_type

Для этого атрибута установлено значение 'ROWS'.

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

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

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

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

Принятые значения для startи endаргументы None, целое число или ноль. Отрицательное целое число для startрезультатов , а дает . Для обоих и вернется ноль . Допускаются положительные целые числа .N precedingNoneUNBOUNDED PRECEDINGstartendCURRENT ROWend

Есть разница в том, что входит. Если указано в режиме, фрейм начинается или заканчивается текущей строкой. Если указано в режиме, кадр начинается или заканчивается на первом или последнем одноранговом узле в соответствии с предложением упорядочивания. Таким образом, вычисляет выражение для строк, которые имеют то же значение, указанное упорядочиванием. Поскольку шаблон включает в себя как точки, так и точки, это можно выразить следующим образом:CURRENT ROWROWSRANGERANGE CURRENT ROWstartend

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.

Expression API

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

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

класс 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( выражения )

Принимает список выражений и сохраняет их так, чтобы 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то же самое, что и self.

get_group_by_cols( псевдоним = Нет )

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

asc( nulls_first = Ложь , nulls_last = Ложь )

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

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

desc( nulls_first = Ложь , nulls_last = Ложь )

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

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

reverse_ordering()

Возвращает selfс любыми изменениями, необходимыми для отмены порядка сортировки в order_byвызове. Например, реализация выражения изменит свое значение на . Модификации требуются только для выражений, реализующих порядок сортировки, например . Этот метод вызывается, когда вызывается в наборе запросов.NULLS LASTNULLS FIRSTOrderByreverse()

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

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

Функция COALESCESQL определяется как получение списка столбцов или значений. Он вернет первый столбец или значение, которого нет 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

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

Copyright ©2021 All rights reserved