How do I get the values of the lastest entries grouped by an attributes using Django ORM?

Question

I have a report model looking a bit like this:

class Report(models.Model):
    date = models.DateField()
    quantity = models.IntegerField()
    product_name = models.TextField()

I know I can get the last entry for the last year for one product this way:

Report.objects.filter(date__year=2009, product_name="corn").order_by("-date")[0]

I know I can group entries by name this way:

Report.objects.values("product_name")

But how can I get the quantity for the last entry for each product ? I feel like I would do it this way in SQL (not sure, my SQL is rusty):

SELECT product_name, quantity FROM report WHERE YEAR(date) == 2009 GROUP_BY product_name HAVING date == Max(date)

My guess is to use the Max() object with annotate, but I have no idea how to.

For now, I do it by manually adding the last item of each query for each product_name I cant list with a distinct.

Answer 1

Not exactly a trivial query using either the Django ORM or SQL. My first take on it would be to pretty much what you are probably already doing; get the distinct product and date pairs and then perform individual queries for each of those.

year_products = Product.objects.filter(year=2009)
product_date_pairs = year_products.values('product').distinct('product'
        ).annotate(Max('date'))
[Report.objects.get(product=p['product'], date=p['date__max']) 
        for p in product_date_pairs]

But you can take it a step further with the Q operator and some fancy OR'ing to trim your query count down to 2 instead of N + 1.

import operator
qs = [Q(product=p['product'], date=p['date__max']) for p in product_date_pairs]
ored_qs = reduce(operator.or_, qs)
Report.objects.filter(ored_qs)