Django Filter Model One-To-Many ratio, the biggest difference between prices

Question

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

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 received:

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

Now I want the best 6 products with the biggest difference between the last two prices.

Can anyone help with this? Hope this makes sense;)

Answer 1

You can use the StdDev (standard deviation) aggregator, so your query might look like this:

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

Best offer price ps[0].min_price

Hope this helps

Answer 2

An easy way is to use a 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 cancel saving and deleting:

 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 ...