django get latest for each group, group by foreign key

Question

Assume I have something like these models:

class Product(models.Model):
  name = models.CharField(max_length=50)

class Sale(models.Model):
  product = models.ForeignKey(Product)
  sale_date = models.DateField()

I want to get the latest sale for each product. Latest means the latest sale_date. Also, in the same query I want to get the number of products sold for each product.

In SQL terms, I need to group by product_id, to count the number of sales for each product (which is the length of each group). And maybe also to order by date (in each group as well) to get only the latest product sale.

With django ORM I realized I need to somehow use annotate() and maybe in combination with values(). But I still haven't figured how.

Assume I have this products table:

| id |   name    |    
===================
| 1  | Book      |  
| 2  | Telephone |

And this sales table:

| id | product_id | sale_date |
=================================
| 1  |      1     | 05-02-2015 |
| 2  |      2     | 04-02-2015 |
| 3  |      2     | 03-02-2015 |
| 4  |      1     | 06-02-2015 |
| 5  |      1     | 01-02-2015 |

I want to get output like:

| product_id |    name   | sale_date  | num_sales |
====================================================
|     1      | Book      | 06-02-2015 |     3     |
|     2      | Telephone | 04-02-2015 |     2     |

Since 06-02-2015 is the latest sale date for product_id=1 (Book), and 04-02-2015 is the latest date for product_id=2 (Telephone).

Answer 1

from django.db.models import Count, Max
query = Product.objects.annotate(num_sales=Count('sale'))
query = query.annotate(latest_sale_date=Max('sale__sale_date'))
for prod in query.all():
    print (prod.pk, prod.name, prod.latest_sale_date, prod.num_sales)

You will get output like:

(1, u'Book', datetime.date(2015, 6, 2), 3)
(2, u'Telephone', datetime.date(2015, 4, 2), 2)

akin to your expected output in the question. Note that whatever kwarg you pass to annotate becomes an attribute on the query result.