Django Filter Model One-To-Many relation, greatest diff between prices

Question

I have a product model with a foreign key to some prices, and I really want to list the products with the "best" offer... How to do that?

class Product(models.Model):
    productname = models.CharField(max_length=1024)

class Price(models.Model):
    product = models.ForeignKey(Product)
    price = models.DecimalField(max_digits=10, decimal_places=2)
    created = models.DateTimeField(auto_now_add=True)

First I want all products with more than one price, that I got:

ps = Product.objects.annotate(c=Count("price")).filter(c__gt=2)

Now i want the best 6 products with the largest diff between the two latest prices.

Can anyone help with that? I hope it makes sense ;)

Answer 1

You can use the StdDev (Standard Deviation) aggregator, so your queryset could look like this:

ps = Product.objects.filter(price__gt=1).annotate(dev=StdDev("price__price"), min_price=Min("price__price")).order_by('-dev')[:6]

the best offer price is ps[0].min_price

Hope this helps

Answer 2

Simple way - use predefined field

class Product(models.Model):
    productname = models.CharField(max_length=1024)
    price_diff = models.DecimalField(max_digits=10, decimal_places=2, default=0)

use signals or override save and delete :

class Price(models.Model):
    product = models.ForeignKey(Product)
    price = models.DecimalField(max_digits=10, decimal_places=2)
    created = models.DateTimeField(auto_now_add=True)
    def save(self, **kwargs):
         ...
         #calaculate you largest diff and put in product
         ...