Django – filter all products that have only empty variations-CodePudding

as the title says I'm trying to filter out all Products where all variations have a stock_quantity of 0. The variations are a separate class with a foreignkey to the Product. The related_name is set to 'variations'.

Here is my attempt:

Product.objects.annotate(variations_count=Count('variations')).filter(variations__stock_quantity=0)

However, it seems that filters out all variations where at least one variation has a stock quantity of 0, not all of them.

Here are my models:

class Product(models.Model):
    name = models.CharField(max_length=50, null=True, blank=True)
    ...


class Variation(models.Model):
    for_product = models.ForeignKey(Product, on_delete=models.CASCADE, related_name='variants')
    stock_quantity = models.IntegerField()

How can I solve this?

Thanks!

CodePudding user response：

You can check if the sum of the quantities is zero, so:

from django.db.models import Sum


Product.objects.alias(
    stock_variations_count=Sum('variations__stock_quantity')
).filter(stock_variations_count=0)

or you can just exclude a Product that has any variant greater than zero:

from django.db.models import Sum


Product.objects.exclude(stock_variations__stock_quantity__gt=0)

the two are not entirely the same for Products with no variant.

or if you want to filter this out, you use:

from django.db.models import Sum


Product.objects.alias(
    stock_variations_count=Sum('variations__stock_quantity')
).filter(stock_variations_count__gt=0)