开发者

How can a do a "greatest-n-per-group" query in django?

开发者 https://www.devze.com 2022-12-17 12:15 出处:网络
(This is the django version of the thread at SQL join: selecting the last records in a one-to-many relationship)

(This is the django version of the thread at SQL join: selecting the last records in a one-to-many relationship)

Suppose I have a table of customers and a table of p开发者_如何学Pythonurchases. Each purchase belongs to one customer. I want to get a list of all customers along with their last purchase. Can it be done without raw SQL and without multiple database queries?


You can't do this in one query in Django. You can get the customer with just the date of their most recent purchase like this:

from django.db.models import Max
customers = Customer.objects.annotate(Max('purchase__date'))

but you don't automatically get access to the actual purchase this way.


You can take a look at similar discussion:

Django Query That Get Most Recent Objects From Different Categories


SELECT  *
FROM    customers с
LEFT JOIN
        purchases p
ON      p.id = 
        (
        SELECT  id
        FROM    purchases pl
        WHERE   pl.customer = c.id
        ORDER BY
                pl.customer DESC, pl.date DESC
        LIMIT 1
        )

Make sure you have a composite index on purchases (customer, date) if your table is InnoDB, or on purchases (customer, date, id) if your table is MyISAM.

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号