How can a do a "greatest-n-per-group" query in django?
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 Maxcustomers = 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 pON 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
.