Django ORM

• Отлаживание ORM запросов
• Вложенные запросы
• Q условия
• Вычислимые поля
• Приведение типов
• Кастомные функции
• Инструкция CASE WHEN
• Группировка через annotate
• Группировка через aggregate
• Использование метода prefetch
• Использование класса Prefetch
• Использование метода raw
• Использование функции RawSQL

Отлаживание ORM запросов

Прежде чем перейти к описанию возможностей Django ORM, хотел бы отметить такой важный момент, как отлаживание запросов. Так как конечным результатом любой конструкции ORM является некоторый SQL запрос, а корректность полученного SQL запроса прямо влияет на точность возвращенных данных и скорость работы, поэтому важно уметь просматривать сгенерированный SQL запрос. Делается это очень просто:

result = Table.objects.values("id")
print(result.query)

в результате увидим что-то в этом роде:

SELECT "table"."id" FROM "table" 

В случаях, когда используются “не ленивые” операции (delete/update) или задействуются prefetch_related, узнать выполненные запросы можно следующим образом:

from django.db import connection
print(connection.queries[-1]) # для последнего запроса

В этом случае надо убедиться, что settings.DEBUG = True, так как скорее всего отладкой вы будете заниматься в тестах или из джанго консоли, этот параметр можно поменять в любой момент, просто написав:

from django.conf import settings
settings.DEBUG = True

Для тестов рекомендуется использовать декоратор:

@override_settings(DEBUG=True)
def test_method(self):
...

Собственно вот. Итак, начну с простого.

Вложенные запросы

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

items = Table2.objects.all()
ids = [i.foreign_id for i in items]
Table1.objects.filter(id__in=ids)

Я, конечно, утрирую, но лучше сделать вот так:

Table1.objects.filter(
    id__in=Table2.objects.values("foreign_id")
)

Мало того, что этот вариант короче, так еще и выполняется всего в один запрос. На SQL выглядит вот так:

SELECT * 
FROM table1 
WHERE id in (
 SELECT foreign_id
 FROM table2 
)

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

Table2.objects.values("foreign_id")

values, в качестве параметра, содержало название ровно одного столбца. Вместо values допустимо использовать values_list. Он также будет вызываться “лениво”. Вложенный запрос тоже может содержать свои вложенные запросы и какие угодно фильтрации/группировки и т. п., но об этом позже. Пойдем дальше.

Q условия

По умолчанию, джанго каждое новое условие в фильтре добавляет к другому через конъюнкцию (то есть через и/and). Например, следующие два ORM запроса:

Table.objects.filter(field1='value1').filter(field2='value2')
Table.objects.filter(field1='value1', field2='value2')

эквивалентны следующему запросу:

SELECT * FROM table WHERE field1=value1 AND field=value2

Но часто возникают ситуации, когда необходимо использовать дизъюнкцию (то есть операцию или/or), для этого как раз подойдут Q функции. Например, следующий ORM запрос:

from django.db.models import Q

Table.objects.filter(Q(field1=1) | Q(field2=2))

эквивалентен вот такому SQL запросу:

SELECT * FROM table WHERE field1=1 OR field2=2

Как видно, оператор | мэпится в OR, по аналогии можно использовать оператор & который будет мэпиться в AND. Это может пригодиться, если вы захотите фильтровать запрос по более сложному условию. Например, запрос:

Table.objects.filter(
    (Q(field1=1) | Q(field2=2)) &
    (Q(field1=3) | Q(field2=4))
)

эквивалентен следующей конструкции на SQL:

SELECT * 
FROM table 
WHERE (field1=1 OR field2=2) AND (field1=3 OR field2=4 )

Несмотря на кажущуюся простоту, функция Q используется достаточно часто и не только для простых задач.

Вычислимые поля

И вот тут начинается самое интересное.

Аннотации

Допустим, необходимо сформировать новое поле на основе значений других полей и не хочется делать это на уровне python, чтобы не тянуть лишние поля, да и просто потому, что в рамках БД это решается проще. Например, простейший вариант - замена всех NULL значений на фразу “без телефона” (здесь может быть любое другое значение), на SQL это выглядит так:

SELECT id, coalesce(phone, 'без телефона') as phone_number 
FROM users

Как мы видим, тут придется использовать функцию coalesce, которая возвращает значения поля phone, если оно не NULL, или значение второго аргумента (в нашем случае “без телефона”). Вот как это будет выглядеть в джанго:

from django.db.models.functions import Coalesce
from django.db.models import F
from django.db.models import Value as V

query = User.objects.annotate(
    phone_number=Coalesce(F("phone"), V("без телефона"))
).values("id", "phone_number")

Разберем по строчкам:

• в первой строчке - импортируем функцию Coalesce;

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

• импортируемая в третьей строке Value, которую принято алиасить в V (по аналогии c F), позволяет нам явно указать, что в качестве второго аргумента мы хотим видеть именно указанное значение, а не значение поля “без телефона”;

• переходим к самому выражению, тут нас встречает замечательнейший метод annotate, который позволяет нам добавлять вычислимые поля. В нашем случае мы добавляем поле phone_number, указываем ему как оно будет вычисляться. Тут отличий от SQL версии почти нет - мы всего лишь оборачиваем аргументы в F и V. А в values мы прямо указываем, какие поля хотим получить в ответе.

Наше новодописанное поле будет автоматически добавлено к списку аргументов. Доступ к полю будет осуществляться стандартным способом, т.е: 

print(query[0]['phone_number'])

В случае, если values явно не указан, также пользуемся классикой: 

query = User.objects.annotate(
    phone_number=Coalesce(F("phone"), V("без телефона"))
)
print(query[0].phone_number)

Может возникнуть вопрос: зачем оборачивать функциями F и V? Дело в том, что по умолчанию функции предполагают, что любая строка - название поля. Тогда, в нашем случае, для первого аргумента это делать не обязательно и сработал бы такой способ: 

query = User.objects.annotate(
    phone_number=Coalesce("phone", V("без телефона"))
)

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

query = User.objects.annotate(
    phone_number=Coalesce("phone", "additional_phone")
)

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

query = User.objects.annotate(
    phone_number=Coalesce(Coalesce("phone", "additional_phone"), V("-"))
)

Хотя, конечно, Coalesce допускает использование нескольких аргументов, возвращая в качестве результата первый, не равный NULL, так что в данной ситуации в этом нет необходимости: 

query = User.objects.annotate(
    phone_number=Coalesce("phone", "additional_phone", V("-"))
)

Приведение типов

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

SELECT coalesce(Cast(integer_field as VARCHAR), '-') as counter 
FROM users

тогда аналогичный запрос будет выглядеть вот так:

from django.db.models.functions import Coalesce
from django.db.models.functions import Cast
from django.db.models import CharField

query = User.objects.annotate(
phone_number=Coalesce(Cast("integer_field", CharField()), V("-"))
)

Стоит обратить внимание, что мы не просто передаем класс TextField в качестве второго параметра, а передаем именно объект этого типа. Также стоит помнить, что если вы по случайно возьмете CharField из django.forms, то получите ошибку.

Кастомные функции

Весь список функций, которые встроены в ORM из коробки можно посмотреть в документации: Но бывают случаи, когда необходимо использовать какую-то кастомную функцию. В таком случае можно пойти двумя путями, либо использовать класс django.db.models.functions.Func напрямую, либо (если ваша функция специфическая) наследоваться от этого класса. В качестве примера, попробуем определить свой личный coalesce. Сначала пойдем простым способом и воспользуемся классом Func напрямую, тогда наш первый запрос с Coalesce будет таким:

from django.db.models import Func

query = User.objects.annotate(
   phone_number=Func(F("phone"), F("additional_phone"), function='coalesce')
)

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

from django.db.models import Func

class MyCoalesce(Func):
   function = "coalesce"

query = User.objects.annotate(
   phone_number=MyCoalesce(F("phone"), F("additional_phone"))
)

Вообще, инструмент переопределения функций достаточно гибкий. Например, возникла необходимость склеивать строковые значения с использованием II. Но для склеивания строк Django ORM предлагает использовать функцию Concat, которая принимает набор аргументов для склеивания. Чтобы обойти такое ограничение мы определили такую функцию: 

class ConcatII(Func):
   function = ""  # функция пустышка
   arg_joiner = " || " # заменили запятую на символ конкатенации

query = User.objects.annotate(
   phone_number=ConcatII(F("phone"), F("additional_phone"))
)

Та да! Такой запрос сгенерирует нам такой SQL: 

SELECT phone || additional_phone 
FROM users

CASE WHEN

Другой важный механизм работы с полями - конструкция CASE WHEN. Допустим, в зависимости от даты регистрации, мы хотим определить является ли пользователь новым или старым:

SELECT CASE WHEN date_registred > '2017-01-01'
        THEN 'new'
      WHEN date_registred < '2014-01-01'
        THEN 'old'
      ELSE '' END
FROM users

Аналогичный запрос на джанго выглядит так:

from django.db.models import Case, When
from django.db.models import CharField
from django.db.models import Value as V

query = User.objects.annotate(
   Case(
       When(date_registred__gt="2017-01-01", then=V("new")),
       When(date_registred__lt="2014-01-01", then=V("old")),
       default="",
       output_field=CharField()
   )
)

В данном случае удобно сформировать условия, а потом передать их в функцию, в качестве массива аргументов: 

whens = [
   When(date_registred__gt="2017-01-01", then=V("new")),
   When(date_registred__lt="2014-01-01", then=V("old")),
]

query = User.objects.annotate(
   Case(
       default="",
       output_field=CharField(),
       *whens,
   )
)

В When также можно использовать функции: 

whens = [
   When(date_registred__lt=F("date_closed"), then=Cast("date_registered", CharField())),
]

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

WHEN date_registerd < date_closed THEN CAST(date_registered as VARCHAR)

Группировка

Annotate

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

SELECT phone, Count('id') as phones_count 
FROM users 
GROUP BY phone

На джанго это будет выглядеть так:

User.objects.annotate(
   phones_count=Count("id")
).values(
   "phone", # это поле которое идет в GROUP BY
   "phones_count"
)

Работает это так: если values идет после annotate и там встречается поле для которого выше в annotate не было использована агрегатная функция, то джанго воспринимает конструкцию как запрос с группировкой. Фильтрация добавляется обычным способом. Соответственно, фильтрации по полям с агрегатной функцией будут мэпиться в HAVING, например:

User.objects.annotate(
   phones_count=Count("id")
).values(
   "phone",
   "phones_count"
).filter(
   phone__contains="495"
).filter(
   phones_count__gt=2
)

На SQL это будет выглядеть так:

SELECT phone, Count('id') as phones_count 
FROM users
 WHERE phone LIKE '%495%' 
GROUP BY phone 
HAVING Count('id') > 2

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

UserPayment.objects.annotate(
   total=Sum("payment") + Sum("nds")
).values(
   "user_id",
   "total"
)

На SQL это будет выглядеть так:

SELECT user_id, payment + nds as total 
FROM users 
GROUP BY user_id

Aggregate

Также, в дажнго предусмотрена функция aggregate - для агрегации по всему запросу; в ответе она возвращает словарь с полями, указанными в aggregate. Например:

UserPayment.objects.aggregate(
   total=Sum("payment1") + Sum("nds"),
   total_bouns=Count("bonus"),
)

Аналог на SQL:

SELECT Sum(payment1) + Sum(nds) as total, Count(bonus) as total_bouns 
FROM user_payments

В ответ получим словарь:

{
 "total": 10000,
 "total_bouns": 10, 
} 

Понятно, что использование values для такого запроса не имеет никакого смысла и поэтому использовать после aggregate его не получится.

Annotate + Aggregate

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

UserPayment.objects.annotate(
   payment=F("payment1") + F("nds")
).aggregate(
   total=Sum("payment")
)

Получим SQL запрос:

SELECT Sum(payment1 + nds) as total 
FROM user_payments

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

UserPayment.objects.aggregate(
   total=Sum(F("payment1") + F("nds"))
)

Prefetch

Метод prefetch_related позволяет нам уменьшить количество запросов к БД, если вы хотите выгрузить связанные с моделью данные в отношении один ко многим или многие ко многим. Допустим, есть у нас две модели:

class Author(models.Model):
   name = models.TextField()

class Book(models.Model):
   title = models.TextField()
   author = models.ForeignKey("Author", related_name="books")

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

authors = Author.objects.all().prefetch_related("books")

При такой конструкции джанго будет использовать два запроса к БД:

• чтобы стянуть всех авторов;

• чтобы стянуть все книги, привязанные к этим авторам (а также это привяжет книги к авторам).

И тогда мы сможем обращаться к книгам автора: 

for author in authors:
   for book in author.books.all():
       print (book)

С точки зрения SQL это будет выглядеть так: первый запрос: 

SELECT * FROM authors 

и второй запрос: 

SELECT * FROM books WHERE author_id in (1,2,3,4…)

То есть во второй запрос будут подставляться уже стянутые в предыдущем запросе идентификаторы. Но тут есть одна тонкость. Допустим, нам захочется выводить только книги, которые начинаются на А. Первое, что придет в голову - написать вот так: 

for author in authors:
   for book in author.books.filter(title__startswith="А"):
       print (book)

И тут мы встретимся с первой неожиданностью. Такая конструкция на каждого автора будет делать дополнительный запрос, чтобы стянуть все привязанные к нему книги начинающиеся на “А”. А то, что вы “спрефетчили” в первой строке вообще будет проигнорировано. Это происходит потому, что данные загруженные через prefetch_related кешируются только для запросов типа all (смотри первый пример). Т.е. никаких фильтров, аннотаций и прочих прелестей использовать нельзя. Вторая идея, которая может возникнуть, выглядит так: 

authors = Author.objects.all().prefetch_related("books")\
    .filter(books__title__startswith="А")

И снова будем неправы, ибо такой запрос не просто отфильтрует нам книги, но также уберет из ответа всех авторов, у которых нет книг начинающихся на “А”. В общем, чтобы работать с данными, загружаемыми через prefetch_related, предусмотрен специальный класс Prefetch, который позволяет нам превратить поведение prefetch_related в некое подобие annotate, но для связных данных.

Класс Prefetch

Чтобы наверняка решить проблему, возникшую выше, необходимо воспользоваться классом Prefetch, который позволяет фильтровать, добавлять аннотации и т. д. к связным наборам. Делается это так:

from django.db.models.query import Prefetch
Author.objects.all.prefetch_realted(
   Prefetch("books", Book.objects.filter(title__startswith="А")),
)
for author in authors:
   for book in author.books.all():
       print (book)

У конструктора Prefetch три параметра:

def __init__(self, lookup, queryset=None, to_attr=None)

lookup - это название связного набора, от которого будем плясать. 

queryset - это ORM запрос, с помощью которого можно модифицировать исходный связный набор. 

to_attr - позволяет сохранить результат запроса в новый атрибут.

Стоит уточнить, что по дизайну в аргументе queryset полагается использовать ORM запрос с той же моделью что и у связанного набора, но если вам вдруг захочется подставить что-то другое, то возможно оно и сработает, но не факт, что так как ожидаемо, или вообще с ошибкой:

FieldError: Cannot resolve keyword u'author' into field

В общем, не надо так.

Также есть ограничение на использование метода values в ORM запросе. Так, попробовав вызвать:

Author.objects.all.prefetch_realted(
   Prefetch("books", Book.objects.filter(title__startswith="А").values("values")),
)

вы получите ошибку:

ValueError: Prefetch querysets cannot use values().

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

Author.objects.all.prefetch_realted(
   Prefetch("books", Book.objects.filter(title__startswith="А"), "books_a"),
   Prefetch("books", Book.objects.filter(title__startswith="Б"), "books_b"),
)

for author in authors:
   for book in author.books_a.all():
       print (book)
   for book in author.books_b.all():
       print (book)

Еще раз прошу обратить внимание, что при доступе к prefetch-наборам, мы всегда обязательно используем метод all().

Метод raw

Это штуковина остается на крайний случай когда все остальные способы уже перепробованны. Главным условием является, чтобы в запросе, в результате присутствовало поле id.

Допустим у вас хитрый join с несколькими условиями, и какое-то поле, которое формируется на основании этого джоина. Что-то в этом роде:

SELECT t.id, coalesce(g.limit, t.limit) as limit_real
FROM table t
 LEFT JOIN groups g ON g.id = t.group_id and g.some_condition = TRUE

items = Table.objects.raw("""
SELECT t.id, coalesce(g.limit, t.limit) as limit_real
FROM table t
 LEFT JOIN groups g ON g.id = t.group_id and g.some_condition = TRUE
""")

for item in items:
    print(item.limit_real)

SELECT id 
FROM table t1
 LEFT JOIN another_table t2 ON t1.related_id = t2.id and t2.some_condition
 LEFT JOIN big_table bt ON t2.related_id = bt.id and bt.another_condition != t2.some_condition
WHERE t1.another_condition is not NULL and bt.is_big_enough = False

from django.db.models.expressions import RawSQL

items = Table.objects.filter(id__in=RawSQL("""
SELECT id 
FROM table t1
 LEFT JOIN another_table t2 ON t1.related_id = t2.id and t2.some_condition
 LEFT JOIN big_table bt ON t2.related_id = bt.id and bt.another_condition != t2.some_condition
WHERE t1.another_condition is not NULL and bt.is_big_enough = False
"""))

Заключение

В целом, я не считаю Django ORM идеальным. В возможностях он сильно проигрывает sqlalchmey, ну и подавно чистому SQL. Но в разработке финансовой системы, в которой я участвую, его хватает в 85% случаях. Оставшиеся 15% - это весьма сложные запросы с использованием кастомных JOIN`ов, CTE и прочих sql-интересностей. Сей пробел нам пока удается компенсировать через использование aldjemy (который и позволяет использовать тот самый sqlalchemy для Django моделей). Да, наверное, можно писать запросы на чистом SQL, там нет ограничений в способах самовыражения и его намного проще отлаживать и профилировать. Но, к сожалению, он не предлагает элегантного способа конструировать запрос и следовать традициям DRY. Да и про маппинг в Django модели не забываем. Так что рассматриваю эту ORM, как вполне удачный компромисс между чистым кодом и предоставляемыми возможностями.

  django, orm, python, sql

  Смотреть все посты