Как использовать подзапрос в django?
Я хочу получить список последней покупки каждого клиента, отсортированного по дате.
Следующий запрос выполняет то, что я хочу, за исключением даты:
(Purchase.objects
.all()
.distinct('customer')
.order_by('customer', '-date'))
Он производит запрос типа:
SELECT DISTINCT ON
"shop_purchase.customer_id"
"shop_purchase.id"
"shop_purchase.date"
FROM "shop_purchase"
ORDER BY "shop_purchase.customer_id" ASC,
"shop_purchase.date" DESC;
Я вынужден использовать customer_id
в качестве первого выражения ORDER BY
из-за DISTINCT ON
.
Я хочу сортировать по дате, так что действительно нужен мне запрос:
SELECT * FROM (
SELECT DISTINCT ON
"shop_purchase.customer_id"
"shop_purchase.id"
"shop_purchase.date"
FROM "shop_purchase"
ORDER BY "shop_purchase.customer_id" ASC,
"shop_purchase.date" DESC;
)
AS result
ORDER BY date DESC;
Я не хочу сортировать, используя python, потому что я до сих пор добрался до страницы, ограничивающей запрос. В базе данных могут быть десятки тысяч строк.
Фактически, в настоящее время он отсортирован в python и вызывает очень длительное время загрузки страницы, поэтому я пытаюсь исправить это.
В принципе, я хочу что-то вроде qaru.site/info/49693/.... Можно ли выразить его с помощью запросов django вместо написания необработанного SQL?
Фактические модели и методы имеют длину в несколько страниц, но вот набор моделей, необходимых для запроса выше.
class Customer(models.Model):
user = models.OneToOneField(User)
class Purchase(models.Model):
customer = models.ForeignKey(Customer)
date = models.DateField(auto_now_add=True)
item = models.CharField(max_length=255)
Если у меня есть такие данные, как:
Customer A -
Purchase(item=Chair, date=January),
Purchase(item=Table, date=February)
Customer B -
Purchase(item=Speakers, date=January),
Purchase(item=Monitor, date=May)
Customer C -
Purchase(item=Laptop, date=March),
Purchase(item=Printer, date=April)
Я хочу получить следующее:
Purchase(item=Monitor, date=May)
Purchase(item=Printer, date=April)
Purchase(item=Table, date=February)
В каждом клиенте покупается не более одной покупки. Приобретение - это самый последний заказчик. Он сортируется по последней дате.
Этот запрос сможет извлечь это:
SELECT * FROM (
SELECT DISTINCT ON
"shop_purchase.customer_id"
"shop_purchase.id"
"shop_purchase.date"
FROM "shop_purchase"
ORDER BY "shop_purchase.customer_id" ASC,
"shop_purchase.date" DESC;
)
AS result
ORDER BY date DESC;
Я пытаюсь найти способ не использовать исходный SQL для достижения этого результата.
Ответы
Ответ 1
Это может быть не совсем то, что вы ищете, но это может приблизиться к вам. Взгляните на Django annotate.
Вот пример того, что может помочь:
from django.db.models import Max
Customer.objects.all().annotate(most_recent_purchase=Max('purchase__date'))
Это даст вам список моделей ваших клиентов, каждый из которых будет иметь новый атрибут, называемый "most_recent_purchase", и будет содержать дату, когда они совершили свою последнюю покупку. Созданный sql выглядит так:
SELECT "demo_customer"."id",
"demo_customer"."user_id",
MAX("demo_purchase"."date") AS "most_recent_purchase"
FROM "demo_customer"
LEFT OUTER JOIN "demo_purchase" ON ("demo_customer"."id" = "demo_purchase"."customer_id")
GROUP BY "demo_customer"."id",
"demo_customer"."user_id"
Другой вариант - добавить свойство модели клиента, которое будет выглядеть примерно так:
@property
def latest_purchase(self):
return self.purchase_set.order_by('-date')[0]
Вам, очевидно, нужно будет обработать случай, когда в этом свойстве нет каких-либо покупок, и это потенциально может не работать очень хорошо (так как вы будете запускать один запрос для каждого клиента, чтобы получить свою последнюю покупку).
Я использовал оба эти метода в прошлом, и они оба отлично работали в разных ситуациях. Надеюсь, это поможет. Удачи!
Ответ 2
Всякий раз, когда возникает сложный запрос на запись с использованием Django ORM, я сначала пытаюсь выполнить запрос в psql (или любом другом клиенте, который вы используете). SQL, который вы хотите, не:
SELECT * FROM (
SELECT DISTINCT ON
"shop_purchase.customer_id" "shop_purchase.id" "shop_purchase.date"
FROM "shop_purchase"
ORDER BY "shop_purchase.customer_id" ASC, "shop_purchase.date" DESC;
) AS result
ORDER BY date DESC;
В вышеприведенном SQL внутренний SQL ищет отдельную комбинацию (customer_id, id и date), и поскольку id будет уникальным для всех, вы получите все записи из таблицы. Я предполагаю, что id является первичным ключом по соглашению.
Если вам нужно найти последнюю покупку каждого клиента, вам нужно сделать что-то вроде:
SELECT "shop_purchase.customer_id", max("shop_purchase.date")
FROM shop_purchase
GROUP BY 1
Но проблема с вышеуказанным запросом заключается в том, что он даст вам только имя и дату клиента. Использование этого не поможет вам найти записи при использовании этих результатов в подзапросе.
Чтобы использовать IN
, вам нужен список уникальных параметров для идентификации записи, например, id
Если в вашем записях идентификатор является серийным ключом, вы можете использовать тот факт, что последняя дата будет также максимальным. Итак, ваш SQL становится:
SELECT max("shop_purchase.id")
FROM shop_purchase
GROUP BY "shop_purchase.customer_id";
Обратите внимание, что я сохранил только одно поле (id) в выбранном предложении, чтобы использовать его в подзапросе, используя IN.
Теперь полный SQL будет выглядеть следующим образом:
SELECT *
FROM shop_customer
WHERE "shop_customer.id" IN
(SELECT max("shop_purchase.id")
FROM shop_purchase
GROUP BY "shop_purchase.customer_id");
и используя Django ORM, он выглядит так:
(Purchase.objects.filter(
id__in=Purchase.objects
.values('customer_id')
.annotate(latest=Max('id'))
.values_list('latest', flat=True)))
Надеюсь, что это поможет!
Ответ 3
У меня подобная ситуация, и я планирую это сделать:
query = Purchase.objects.distinct('customer').order_by('customer').query
query = 'SELECT * FROM ({}) AS result ORDER BY sent DESC'.format(query)
return Purchase.objects.raw(query)
Поверхность дает мне запрос, который я хочу. Недостатком является то, что это необработанный запрос, и я не могу добавить никаких других фильтров запросов.
Ответ 4
Это мой подход, если мне нужно некоторое подмножество данных (N элементов) вместе с запросом Django. Это пример использования PostgreSQL и удобная функция json_build_object()
(Postgres 9.4+), но так же вы можете использовать другую функцию агрегации в другой системе баз данных. Для более старых версий PostgreSQL вы можете использовать комбинацию функций array_agg()
и array_to_string()
.
Представьте, что у вас есть модели Article
и Comment
, и вместе с каждой статьей в списке вы хотите выбрать 3 последних комментария (измените LIMIT 3
, чтобы настроить размер подмножества или ORDER BY c.id DESC
, чтобы изменить сортировку подмножества).
qs = Article.objects.all()
qs = qs.extra(select = {
'recent_comments': """
SELECT
json_build_object('comments',
array_agg(
json_build_object('id', id, 'user_id', user_id, 'body', body)
)
)
FROM (
SELECT
c.id,
c.user_id,
c.body
FROM app_comment c
WHERE c.article_id = app_article.id
ORDER BY c.id DESC
LIMIT 3
) sub
"""
})
for article in qs:
print(article.recent_comments)
# Output:
# {u'comments': [{u'user_id': 1, u'id': 3, u'body': u'foo'}, {u'user_id': 1, u'id': 2, u'body': u'bar'}, {u'user_id': 1, u'id': 1, u'body': u'joe'}]}
# ....