django product price tracker: getting the amount and date of the all time max and min price

Question

I'm trying to build a django app where I can track product prices over time. The app fetches new prices routinely, graphs them and shows the recent history of price changes.

I'm checking the price once a day and saving that price plus the date timestamp to my models.

models.py

Class Product(models.Model):  
    title = models.CharField(max_length=255)

Class Price(models.Model):  
    product = models.ForeignKey(Product, on_delete=models.CASCADE)
    date_seen = models.DateTimeField(auto_now_add=True)
    price = models.IntegerField(blank=True, null=True)

Along with the current price of a product I'd also like to show the max and min over all the price data I've collected. I want to get the value and also the date it was at that value. So far I can get the value but I can't get the corresponding date. I'm using this:

def price_hla(self):
    return Product.objects.filter(price__product=self).aggregate(high_price=Max('price__price'), low_price=Min('price__price'), avg_price=Avg('price___price'))

Any advice? Thanks in advance!

EDIT: Based on responses I have the following. My problem is I'm getting the MAX price and MAX date independent of each other. I want the MAX price with that max price's date in the same response.

def price_hla(self):
    return 
    Product.objects.filter(price__product=self)[:1].annotate(Max('price__price'), Max('price__date_seen'))`

Possible duplicate of [How to make Django Queryset that selects records with max value within a group](https://stackoverflow.com/questions/32359954/how-to-make-django-queryset-that-selects-records-with-max-value-within-a-group) — solarissmoke, Oct 26 '17 at 02:50
This this exactly the same? It seems they're trying to find the max and date for specific product without a table join. — adamkost, Oct 26 '17 at 03:29
The same logic applies, whether or not there is a table join. You just have a foreign key instead of an integer. — solarissmoke, Oct 26 '17 at 03:57
Hm, okay. I edited my original post with what I have now but my problem is I'm getting the MAX price and MAX date independent of each other. I want the MAX price with that max price's date in the same response. — adamkost, Oct 26 '17 at 21:50

score 0 · Answer 1 · answered Oct 27 '17 at 02:19

Try this:

Product.objects.filter(price__product=self).annotate(
        high_price=Max('price__price'), 
    ).filter(price=F('max_price'))

Which should give you the max price and date in the resulting objects.

I can't think of a way to simultaneously find the minimum price/date in the same query though. I also have a feeling that this is going to be very slow if you have a large number of items.

score 0 · Accepted Answer · answered Oct 27 '17 at 23:05

Figured this out and I'm getting what I want. If anyone reads this I'd love feedback about if this is best practice or if I'm going to be overloading my database.

Because I needed both the actual price and the date the price was max I needed to return the whole Price Object. So I wrote some QuerySets on my DetailView by overwriting the default get_context_data method.

views.py

class ProductDetailView(DetailView):
    model = Product

    def get_context_data(self, **kwargs):
        context = super(ProductDetailView, self).get_context_data(**kwargs)      
        context['high'] = Price.objects.filter(product_id=self.get_object()).order_by('price').last()
        context['low'] = Price.objects.filter(product_id=self.get_object()).order_by('-price').last()
        context['avg'] = Price.objects.filter(product_id=self.get_object()).aggregate(avg_price=Avg('price'))

Then I pulled it in to my templates using high.price and high.date_seen, etc.

django product price tracker: getting the amount and date of the all time max and min price

2 Answers2