Django ORM join on subquery
У меня есть две модели:
class Order(models.Model):
value= models.FloatField()
customer = models.ForeignKey(
Customer,
on_delete=models.CASCADE,
related_name="orders"
)
class Customer(models.Model):
name = models.CharField(max_length=300, unique=True)
и я хочу аннотировать сумму общих заказов, приобретенных клиентом, это может быть сделано довольно просто с помощью этого запроса:
customer_total_subquery = Subquery(
Customer.objects.filter(pk=OuterRef("customer"))
.annotate(total=Coalesce(Sum("orders__value"), 0.0, output_field=FloatField()))
.values("total")
)
Order.objects.select_related("customer").all().annotate(customer_total=customer_total_subquery)
Проблема в том, что для всего 37000 заказов этот запрос занимает 4 минуты. Произведенный запрос:
SELECT "order"."id",
(SELECT COALESCE(SUM(U1."order_value"), 0.0) AS "total"
FROM "customer" U0 LEFT OUTER JOIN "order" U1 ON (U0."id" = U1."customer_id")
WHERE U0."id" = "order"."customer_id"
GROUP BY U0."id") AS "customer_total"
FROM "order" LEFT OUTER JOIN "customer" ON ("order"."customer_id" = "customer"."id")
Мы видим, что есть подзапрос, который выполняется один раз на строку, поэтому подзапрос customer_total выполняется 37000 раз.
Идеальное решение - запросить только один раз и использовать эти результаты для добавления к основному запросу, запрос ниже занимает менее 100 мс:
SELECT o.id, o.customer_id, subquery.customer_total
from order as o
left join
(
select b.id, SUM(inner_order.value) as customer_total
from customer b
left join order inner_order on (inner_order.customer_id= b.id)
group by b.id
) subquery on (subquery.id = o.customer_id)
Можно ли выполнить этот запрос (join on subquery) с помощью Django ORM?
Это можно сделать гораздо проще и эффективнее, вы можете аннотировать ваше отношение напрямую:
Order.objects.select_related("customer").annotate(
customer_total=Coalesce(Sum("customer__orders__value"),
0.0, output_field=FloatField())
).all()
Совершенно одинаковый запрос на двух таблицах с 100k+ записями работает за 500 мс на моем ноутбуке (и большая часть времени была потрачена на передачу данных).
Кстати, ваша конкретная задача довольно странная. Я не совсем понимаю, почему вы думаете, что вам нужно Customer
для каждой записи Order
. Вы можете собрать всех клиентов в дикту с ID в качестве ключа, и собрать любую информацию, которая вам понадобится позже. Например:
customer_by_id = {c.id: c for c in Customer.objects.annotate(
order_total=Coalesce(Sum("orders__value"),
0.0, output_field=FloatField())
).all()}