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

Question

(This is the django version of the thread at http://stackoverflow.com/questions/2111384/)

Suppose I have a table of customers and a table of purchases. 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?

Answer 1

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.

Answer 2

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.

Answer 3

You can take a look at similar discussion:

http://stackoverflow.com/questions/2074514/django-query-that-get-most-recent-objects-from-different-categories/2076665#2076665