Django - How to get the maximum value after distinct on fields

Question

I have those models:

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

class Price(models.Model):
    product = ForeignKey(Product)
    store = CharField(max_length=128)
    price = DecimalField(max_digits=10, decimal_places=2)
    date = DateField()

the products table contains some products (...)

and the price table gets update each time a store change the product price.

I want to get the latest price ("current price") from every store, and then get the maximum and the average price of each product.

I tried it first for one product:

Price.objects.filter(product__id=42).order_by('store','-date').distinct('store').annotate(Max('price'))

but got:

NotImplementedError: annotate() + distinct(fields) is not implemented.

I also tried to add order_by('-price') to get the maximum, but it ordered first by date, so it's useless.

Any suggestion how to do it without multiple queries and nasty loops?

---

EDIT: To make things more clearer, here is a sample of a price table:

 id  |             date              |    store    | price | product_id 
-----+-------------------------------+-------------+-------+------------
 107 | 2017-08-20 00:36:21+03        | shufersal   | 3.510 |         51
 128 | 2017-08-20 01:57:07+03        | shufersal   | 4.360 |         51
 154 | 2017-08-20 01:58:04+03        | mega        | 3.900 |         51
 157 | 2017-08-23 15:15:03+03        | mega        | 3.220 |         51
 189 | 2017-08-23 15:15:03+03        | tivtaam     | 3.480 |         51
 198 | 2017-08-23 15:40:42+03        | tivtaam     | 3.510 |         51
 219 | 2017-08-23 20:41:22+03        | ramilevi    | 4.500 |         51
 244 | 2017-08-23 21:17:54+03        | ramilevi    | 3.545 |         51

Rows 128,157,198,244 are the latest update of each store. I want to get the maximum price of those rows (4.360), and the average (3.659).

Answer 1

Hi can you try like this

Price.objects.filter(product__id=42).order_by('store','-date').annotate(max_value=Max('price', distinct = True))

i think it must be working