(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
.
精彩评论