CODEWITHSUNDEEP
pythondjango
Tri nguyen minh
Tri nguyen minh

Reputation: 129

Django - How to SUM the related Model field with Filter in Annotate?

I've been facing this issue. I have 2 models with relationship like below, I tried to Sum all the PRICE of each author with filter: sale_type=sl01 and I was stuck all day. Can someone point the right way for it to solve it, many thanks in advance,

from django.db.models import Q, Subquery, OuterRef

    SALE_CHOICES = (
        ("sl01", "sl01"),
        ("sl02", "sl02")
    )
    
    
    class Author(models.Model):
        created = models.DateTimeField(auto_now_add=True)
        name = models.CharField(max_length=50, default='', null=True)
        password = models.CharField(max_length=20, default='', null=True)
        phone = models.CharField(max_length=15, default='', null=True)
        address = models.CharField(max_length=100, default='', null=True)
    
    
    class Sale(models.Model):
        created = models.DateTimeField(auto_now_add=True)
        author = models.ForeignKey(Author, on_delete=models.CASCADE, null=True)
        price = models.DecimalField(max_digits=10, decimal_places=2, null=True)
        sale_type = models.CharField(choices=SALE_CHOICES, default='sl01', max_length=10)

    data = Author.objects.annotate(total_amount=Subquery(Sale.objects.filter(author=OuterRef('author'), sale_type='sl01').aggregate(total_amount=Sum('price'))['total_amount']))

Upvotes: 2

Views: 1481

Answers (1)

Abdul Aziz Barkat
Abdul Aziz Barkat

Reputation: 21797

You can pass a filter keyword argument to aggregation functions to filter on the aggregations. See Filtering on annotations [Django docs]

from django.db.models import Q, Sum


data = Author.objects.annotate(total_amount=Sum('sale__price', filter=Q(sale__sale_type='sl01')))

Upvotes: 4

Related Questions