CODEWITHSUNDEEP
pythondjangomodelmany-to-many
Wojtek
Wojtek

Reputation: 2524

Annotate using an extra field from a ManyToMany model in Django

Straight to the point:
models:

class Shop(models.Model):
    name = models.CharField(max_length=255)        

class Product(models.Model):
    name = models.CharField(max_length=255)
    shops = models.ManyToManyField(Shop, through='ProductShop', related_name='products')

class ProductShop(models.Model):
    product = models.ForeignKey(Product)
    shop = models.ForeignKey(Shop)
    price = models.DecimalField(max_digits=20, decimal_places=2)

Now I'd like to get a list of Products ordered by the lowest price available in all the Shops that sell this Product. I've always used annotate for such queries, but here I can't think of any solution. Here's roughly what I'd like to get:

products = Product.objects.annotate(price=SOMETHING_TO_GET_THE_LOWEST_PRICE_PER_PRODUCT).order_by('price')

Is there any nice solution for that? I know about extra, but wouldn't like to write plain SQL. I did my best to find an answer, but couldn't google my specific problem.

Thanks!

Upvotes: 0

Views: 471

Answers (2)

Wojtek
Wojtek

Reputation: 2524

At last solved it with a slight workaround:

class Product(models.Model):
    name = models.CharField(max_length=255)
    shops = models.ManyToManyField(Shop, through='ProductShop', related_name='products')
    def lowest_possible_price(self):
        shops = self.shops.all()
        if not shops:
            return "N/A"
        return reduce(lambda x, y: min(x, ProductShop.objects.get(shop__pk=y.id, product__pk=self.id).price), 
                shops, ProductShop.objects.get(shop__pk=shops[0].id, product__pk=self.id).price)



products = sorted(Product.objects.all(),
        key = lambda product : product.lowest_possible_price())

Still bothers me if there'a one-line solution with no helper function (lowest_possible_price here)

Upvotes: 0

catherine
catherine

Reputation: 22808

You mean:

products = ProductShop.objects.filter(
   shop__in=Shop.objects.filter()).annotate(Min(price)).order_by('price')

{% for ps in productshop %}
    Shop: {{ ps.product.shop }}
    Product: {{ ps.product }}
    Minimum Price: {{ ps.price }}
{% endfor %}

Upvotes: 1

Related Questions