Django 1.11. Аннотирование агрегата подзапроса
Это отличная черта, на которой я в настоящее время нахожусь и быстро истекаю кровью. Я хочу аннотировать подзапрос-агрегат на существующий набор запросов. Делать это до 1.11 означало либо пользовательский SQL, либо удар по базе данных. Вот документация для этого и пример из нее:
from django.db.models import OuterRef, Subquery, Sum
comments = Comment.objects.filter(post=OuterRef('pk')).values('post')
total_comments = comments.annotate(total=Sum('length')).values('total')
Post.objects.filter(length__gt=Subquery(total_comments))
Они аннотируют совокупность, что мне кажется странным, но что угодно.
Я борюсь с этим, так что я довожу его до самого простого примера из реальной жизни, для которого у меня есть данные. я имею Carpark ы, которые содержат много Space s. использование Book→Author если это делает вас счастливее, но пока - я просто хочу аннотировать счет соответствующей модели, используя Subquery *.
spaces = Space.objects.filter(carpark=OuterRef('pk')).values('carpark')
count_spaces = spaces.annotate(c=Count('*')).values('c')
Carpark.objects.annotate(space_count=Subquery(count_spaces))
Это дает мне прекрасный ProgrammingError: more than one row returned by a subquery used as an expression и в моей голове эта ошибка имеет смысл. Подзапрос возвращает список пробелов с аннотированной суммой.
Пример предполагал, что случится какая-то магия, и я получу число, которое смогу использовать. Но что здесь не происходит? Как аннотировать совокупные данные подзапроса?
Хм, что-то добавляется в мой запрос SQL...
Я построил новую модель Carpark/Space, и она сработала. Итак, следующий шаг - выяснить, что отравляет мой SQL. По совету Лорана я взглянул на SQL и попытался сделать его более похожим на версию, которую они опубликовали в своем ответе. И вот где я нашел реальную проблему:
SELECT "bookings_carpark".*, (SELECT COUNT(U0."id") AS "c"
FROM "bookings_space" U0
WHERE U0."carpark_id" = ("bookings_carpark"."id")
GROUP BY U0."carpark_id", U0."space"
)
AS "space_count" FROM "bookings_carpark";Я выделил это, но это тот подзапрос GROUP BY ... U0."space", Это перенастраивает оба по некоторым причинам. Расследования продолжаются.
Редактировать 2: Хорошо, просто глядя на подзапрос SQL, я могу увидеть эту вторую группу, пройдя через ☹
In [12]: print(Space.objects_standard.filter().values('carpark').annotate(c=Count('*')).values('c').query)
SELECT COUNT(*) AS "c" FROM "bookings_space" GROUP BY "bookings_space"."carpark_id", "bookings_space"."space" ORDER BY "bookings_space"."carpark_id" ASC, "bookings_space"."space" ASC
Редактировать 3: Хорошо! Обе эти модели имеют порядок сортировки. Они переносятся в подзапрос. Именно эти приказы раздувают мой запрос и нарушают его.
Я думаю, это может быть ошибка в Django, но если не считать удаления Meta-order_by на обеих этих моделях, есть ли способ отменить запрос во время запроса?
* Я знаю, что мог бы просто прокомментировать граф для этого примера. Моя настоящая цель использования этого - гораздо более сложный подсчет фильтров, но я даже не могу заставить это работать.
7 ответов
Также возможно создать подкласс Subquery, который изменяет SQL, который он выводит. Например, вы можете использовать:
class SQCount(Subquery):
template = "(SELECT count(*) FROM (%(subquery)s) _count)"
output_field = models.IntegerField()
Затем вы используете это как оригинал Subquery учебный класс:
spaces = Space.objects.filter(carpark=OuterRef('pk')).values('pk')
Carpark.objects.annotate(space_count=SQCount(spaces))
Вы можете использовать этот прием (по крайней мере, в postgres) с рядом функций агрегирования: я часто использую его для построения массива значений или их суммирования.
Shazaam! Согласно моим правкам, из моего подзапроса выводился дополнительный столбец. Это должно было облегчить заказ (который просто не требуется в COUNT).
Мне просто нужно было удалить предписанный мета-порядок из модели. Вы можете сделать это, просто добавив пустой .order_by() в подзапрос. В моем коде это означало:
spaces = Space.objects.filter(carpark=OuterRef('pk')).order_by().values('carpark')
count_spaces = spaces.annotate(c=Count('*')).values('c')
Carpark.objects.annotate(space_count=Subquery(count_spaces))
И это работает. Superbly. Так раздражает.
Проблема в том, что Django добавляет group by, как только видит, используя совокупность функций. Таким образом, вы можете просто создать свою собственную агрегатную функцию, но так, чтобы Django считал ее не агрегированной. Именно так:
total_comments = Comment.objects.filter(
post=OuterRef('pk')
).order_by().annotate(
total=Func(F('length'), function='SUM')
).values('total')
Post.objects.filter(length__gt=Subquery(total_comments))
Таким образом вы получите такой SQL-запрос:
SELECT "testapp_post"."id", "testapp_post"."length"
FROM "testapp_post"
WHERE "testapp_post"."length" > (SELECT SUM(U0."length") AS "total"
FROM "testapp_comment" U0
WHERE U0."post_id" = "testapp_post"."id")
Таким образом, вы даже можете использовать агрегированные подзапросы в агрегатных функциях. Например, вы можете не только подсчитать количество рабочих дней между двумя датами, исключая выходные и праздничные дни, но также агрегировать и суммировать их по сотрудникам:
class NonWorkDay(models.Model):
date = DateField()
class WorkPeriod(models.Model):
employee = models.ForeignKey(User, on_delete=models.CASCADE)
start_date = DateField()
end_date = DateField()
number_of_non_work_days = NonWorkDay.objects.filter(
date__gte=OuterRef('start_date'),
date__lte=OuterRef('end_date'),
).annotate(
cnt=Func('id', function='COUNT')
).values('cnt')
WorkPeriod.objects.values('employee').order_by().annotate(
number_of_word_days=Sum(F('end_date__year') - F('start_date__year') - number_of_non_work_days)
)
Я только что столкнулся с ОЧЕНЬ похожим случаем, когда мне нужно было забронировать места на мероприятия, где статус бронирования не отменен. После нескольких часов попыток разобраться в проблеме, вот что я видел в качестве основной причины проблемы:
Предисловие: это MariaDB, Django 1.11.
Когда вы аннотируете запрос, он получает GROUP BY пункт с полями, которые вы выбираете (в основном то, что в вашем values() выбор запроса). После расследования с помощью инструмента командной строки MariaDB, почему я получаю NULLс или NoneПо результатам запроса я пришел к выводу, что GROUP BY пункт приведет к COUNT() возвращать NULLs.
Затем я начал погружаться в QuerySet интерфейс, чтобы увидеть, как я могу вручную, принудительно удалить GROUP BY из запросов к БД, и придумал следующий код:
from django.db.models.fields import PositiveIntegerField
reserved_seats_qs = SeatReservation.objects.filter(
performance=OuterRef(name='pk'), status__in=TAKEN_TYPES
).values('id').annotate(
count=Count('id')).values('count')
# Query workaround: remove GROUP BY from subquery. Test this
# vigorously!
reserved_seats_qs.query.group_by = []
performances_qs = Performance.objects.annotate(
reserved_seats=Subquery(
queryset=reserved_seats_qs,
output_field=PositiveIntegerField()))
print(performances_qs[0].reserved_seats)
Таким образом, в основном, вы должны вручную удалить / обновить group_by поле в наборе запросов подзапроса, чтобы он не имел GROUP BY добавлен на время исполнения. Кроме того, вам нужно будет указать, какое поле вывода будет иметь подзапрос, так как кажется, что Django не может автоматически его распознать и вызывает исключения при первой оценке набора запросов. Интересно, что вторая оценка проходит без него.
Я считаю, что это ошибка Django или неэффективность подзапросов. Я создам сообщение об ошибке об этом.
Изменить: отчет об ошибке здесь.
Решение, которое будет работать для любого общего агрегирования, может быть реализовано с использованием Window занятия от Django 2.0. Я добавил это и к билету на трекер Django.
Это позволяет агрегировать аннотированные значения путем вычисления агрегирования по разделам на основе внешней модели запроса (в предложении GROUP BY), а затем аннотировать эти данные для каждой строки в наборе запросов подзапроса. Подзапрос может затем использовать агрегированные данные из первой возвращенной строки и игнорировать другие строки.
Performance.objects.annotate(
reserved_seats=Subquery(
SeatReservation.objects.filter(
performance=OuterRef(name='pk'),
status__in=TAKEN_TYPES,
).annotate(
reserved_seat_count=Window(
expression=Count('pk'),
partition_by=[F('performance')]
),
).values('reserved_seat_count')[:1],
output_field=FloatField()
)
)
Если я правильно понимаю, вы пытаетесь посчитать Space доступны в Carpark, Подзапрос кажется излишним для этого, только старый добрый аннот должен сделать свое дело:
Carpark.objects.annotate(Count('spaces'))
Это будет включать в себя spaces__count ценность в ваших результатах.
Хорошо, я видел вашу записку...
Я также смог выполнить ваш же запрос с другими моделями, которые у меня были под рукой. Результаты одинаковы, поэтому запрос в вашем примере выглядит нормально (протестировано с Django 1.11b1):
activities = Activity.objects.filter(event=OuterRef('pk')).values('event')
count_activities = activities.annotate(c=Count('*')).values('c')
Event.objects.annotate(spaces__count=Subquery(count_activities))
Может быть, ваш "самый простой пример из реального мира" слишком прост... вы можете поделиться моделями или другой информацией?
"работает для меня" не очень помогает. Но. Я попробовал ваш пример на некоторых моделях, которые мне пригодились (Book -> Author типа), у меня отлично работает в django 1.11b1.
Вы уверены, что используете это в правильной версии Django? Это тот код, который вы запускаете? Вы на самом деле тестируете это не на carpark но какая-то более сложная модель?
Может быть, попытаться print(thequery.query) чтобы увидеть, какой SQL он пытается запустить в базе данных. Ниже приведено то, что я получил с моими моделями (отредактировано под ваш вопрос):
SELECT (SELECT COUNT(U0."id") AS "c"
FROM "carparks_spaces" U0
WHERE U0."carpark_id" = ("carparks_carpark"."id")
GROUP BY U0."carpark_id") AS "space_count" FROM "carparks_carpark"
Не совсем ответ, но, надеюсь, это поможет.