如何在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;
由於DISTINCT ON,我被迫使用customer_id作為第一個ORDER BY表示式.
我想按日期排序,所以我真正需要的查詢應該是這樣的:
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中排序,並導致非常長的頁面載入時間,所以這就是為什麼我試圖解決這個問題.
基本上我想要這樣的東西ofollow,noindex" target="_blank">http://stackoverflow.com/a/9796104/242969 .可以用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實現此結果的方法.
.
這是一個可能有幫助的例子:
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]
您顯然需要處理此屬性中沒有任何購買的情況,這可能不會很好(因為您將為每個客戶執行一個查詢以獲得最新的購買).
過去我已經使用了這兩種技術,並且在不同的情況下都能正常工作.我希望這有幫助.祝你好運!
http://stackoverflow.com/questions/14331224/how-to-use-subquery-in-django