Демистификация сложных запросов для Django ORM
Мотивация
Итак, я работал над созданием своего личного проекта, когда я натолкнулся на препятствие при написании определенного запроса. Я хорошо разбираюсь в SQL как таковом, но я почувствовал себя растерянным, когда попытался преобразовать эту логику в классическое объектно-реляционное сопоставление (ORM) Django. Это заставило меня пройти через несколько болезненных часов, пока один джентльмен наконец не ответил на мой конкретный вопрос о переполнении стека. После этого ответа я решил просмотреть несколько ресурсов и аккуратную документацию Django, чтобы составить небольшое руководство по созданию вашей сложной логики SQL в рамках предоставленной ORM.
Вступление
Эта статья предполагает базовые знания Django и основы SQL. Основные темы, которые я стремлюсь охватить: агрегирование, функции, подзапросы, оконные функции. Я стремлюсь добавить больше концепций, поскольку чувствую личную потребность в их использовании. Эмпирическое правило: любое выражение, которое является более сложным или требует больше времени, чем ваша доступная пропускная способность? Просто напишите необработанный SQL в курсор Django. Это не лучший метод, так как это довольно уязвимые SQL-инъекции. Тем не менее, при соблюдении надлежащих правил и функционального программирования атаки с использованием инъекций также могут быть защищены.
В основном я буду говорить о модели вопроса и модели ответа, схема которой определена на самом верхнем изображении выше.
Агрегирование
Как и в обычном SQL, агрегирование может выполняться либо для получения сводного результата по всему набору запросов, либо для получения индивидуального отчета для каждого объекта в наборе запросов.
Aggregate()
Предположим, у вас есть модель вопроса, в которой каждый вопрос поддерживает поле ManyToMany с пользователем, обозначающее отношения «Follower». Если мне нужна сводка голосов, полученных за вопрос, я могу выполнить следующий запрос:
>>> from django.db.models import Count
>>> Question.objects.aggregate(
avg_followers = Avg(Count('followers')),
highest_followers = Max(Count('followers'))
lowest_followers = Min(Count('followers'))
){'avg_followers' : 14.23, 'highest_followers' : 1523, 'lowest_followers' : 0}
Обратите внимание, как мне пришлось импортировать функцию Count и заключить в нее поле «followers». Followers - это ManyToManyField, функция Count возвращает количество пользователей, которые подписались на вопрос. На основе этого результата по полному набору запросов находятся соответствующие минимальные, максимальные и средние подписчики.
Annotate()
Как вкратце объяснено выше, аннотация используется для создания индивидуальной сводки для каждого объекта в наборе запросов. Это означает, что результат агрегирования вычисляется для каждой отдельной записи, которая может быть или не совпадать. Если бы я хотел создать счетчик «за» для всех проголосовавших за ответ в модели ответа:
>>> from django.db.models import Count
>>> query = Answers.objects.annotate(upvotes = Count('upvoters'))
>>> query[0]
<Answer : 1>
>>> query[0].upvotes
143
>>> query[1]
<Answer : 2>
>>> query[1].upvotes
23
F() выражения
Выражение F() представляет значение поля/аннотированного столбца модели. Он используется для эффективного обращения к значениям полей модели без фактического извлечения их в память Python. В результате работу выполняет база данных, а не Python.
>>> from django.db.models import F
>>> query = Answer.objects.get(author__username='keshavvinayak01')
>>> query.answer
Yes, Math is in fact related to Science.
>>> query.update(answer = F('answer') + " [Anonymous]")
>>> query.answer
Yes, Math is in fact related to Science. [Anonymous]
Мы можем использовать эти выражения в аннотациях, фильтрах или ссылках на внешний ключ.
>>> from django.db.models import F
# В аннотациях
>>> answer = Answer.objects.annotate(
interest = Count(F('followers') + Count(F('requested')
)
# В фильтрах
>>> own_qa = Answer.objects.filter(
question__asker = F('author')
)
# Ссылка на внешний ключ
>>> answer = Answer.objects.annotate(
question_id = F('question')
)
>>> answer.question
Is Math related to Science?
>>> answer.question_id
3
Func() выражения
Эти выражения включают функции базы данных, такие как LOWER или COALESCE. Вот несколько примеров:
LOWER
>>> from django.db.models import F, Func, Coalesce
>>> quest = Question.objects.annotate(
lower_question = Func(F('question')),
function = 'LOWER'
)
# Все тексты вопросов будут в нижнем регистре.
COALESCE
>>> from datetime import datetime
>>> q = Answer.objects.annotate(
updated_at = Coalesce(F('updated_at'), datetime.now())
)
В первом результате запроса TextField вопроса преобразуется в нижний регистр. Это эквивалентно выбору LOWER (question.question) в SQL. Во втором запросе мы просто устанавливаем значение поля updated_at на текущую дату и время, если оно равно нулю, иначе просто получаем его.
Subquery() выражения
Мы можем добавить явный подзапрос в наш набор запросов, используя выражения Subquery(). Например, если мы хотим получить вопрос только с ответом, получившим наибольшее количество голосов, нам нужно будет включить результат подзапроса, выбирающего такой ответ.
>>> from django.db.models import OuterRef, Subquery
>>> answer_subquery = Answer.objects.filter(
question=OuterRef('pk')).annotate(
upvotes=Count(F('upvoters')
)
).order_by('-upvotes')# What's happening here?
>>> questions = Questions.objects.annotate(
answer=Subquery(answer_subquery.values('answer')[-1])
)
Давайте разберемся с этим до необработанного SQL. Мы хотели получить каждый вопрос вместе с ответами, получившими наибольшее количество голосов. Итак, сначала мы создали подзапрос, который извлекает каждый ответ на заданный вопрос, указанный в OuterRef, а затем сортирует их в порядке убывания в зависимости от количества полученных ими голосов.
Во второй части мы добавляем аннотированное поле ответа, которое в основном является результатом подзапроса, ограниченного 1, чтобы получить ответ с наибольшим количеством голосов.
Для положительных голосов ведется отдельная таблица. Можно легко подсчитать группировку по ответам. Для простоты этот запрос пока можно проигнорировать.
SQL может выглядеть примерно так:
# Отсутствуют некоторые подробности о проголосовавших, так как это поле "многие ко многим".
SELECT "question"."id", (
SELECT "answer"."answer",COUNT("answer.upvoters") as "upvotes"
FROM "answer"
WHERE "answer"."question" = "question"."id"
GROUP BY 1
ORDER BY COUNT("answer.upvoters") DESC
LIMIT 1
) as "answer" from "question"
Помимо этих основных концепций, вы можете самостоятельно писать взаимосвязанные запросы, если поняли все вышеперечисленное. Вы можете создавать агрегаты внутри подзапросов, подзапросов с фильтрами и т.д.
Иногда вам может потребоваться выполнить запросы, которые не точно соответствуют моделям, или напрямую выполнить запросы на обновление, вставку или удаление. В этом случае вы можете получить прямой доступ к базе данных, полностью отклонившись от слоя модели. Пример:
from django.db import connection
def my_custom_sql(self):
with connection.cursor() as cursor:
cursor.execute("""
UPDATE x
SET a = 1
WHERE bar = %s
""",[self.a])
cursor.execute("""
SELECT foo
FROM x
WHERE bar = %s
""", [self.a])
row = cursor.fetchone()
return row
Объект django.db.connection представляет соединение с базой данных по умолчанию. Чтобы использовать соединение с базой данных, вызовите connection.cursor(), чтобы получить объект курсора. Затем вызовите cursor.execute (SQL, <params>) для выполнения SQL и cursor.fetchone() или cursor.fetchall() для возврата результирующих строк.
Заключение
В этом кратком руководстве мы рассмотрели некоторые основы, которые являются ключевыми для выполнения сложных взломов SQL, а также, наконец, обсудили, как мы можем выполнять пользовательский SQL. Я настоятельно рекомендую просмотреть официальную документацию Django, чтобы получить больше информации о других доступных функциях и приемах, которые предлагает Django ORM, если эта статья не может что-то объяснить. Надеюсь, это помогло, пожалуйста, оставляйте отзывы в случае каких-либо сомнений или отзывов. Спасибо за прочтение!
Вернуться на верх