pkdkk pkdkk - 1 year ago 104
Python Question

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

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??, hope it makes sense ;)

Answer Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download