CODEWITHSUNDEEP
django
Russell
Russell

Reputation: 1814

Get annotate sum with calculation in django

I've data like below. Need to get total value by calculating price - discount% and sum of all. How to use aggregate here

Data

models.py

class Order(TimeStamp):    
    code = models.CharField(max_length=255, null=True, blank=True)

class OrderItem(models.Model):
    order = models.ForeignKey(Order, on_delete=models.CASCADE, null=True, blank=True, related_name='order_item')
    name = models.CharField(max_length=255, null=True, blank=True)
    discount = models.FloatField(null=True, blank=True)
    price = models.FloatField(null=True, blank=True)

    def __str__(self):
        return self.name

Upvotes: 0

Views: 619

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 477814

You can calculate the discounted price for each item through .annotate(…) [Django-doc]:

from django.db.models import F, Sum

OrderItem.objects.annotate(
    total=F('price') * 0.01 * (100-F('discount'))
)['total']

If you want to sum up the discounted prices, we can use a Sum expression [Django-doc]:

from django.db.models import F, Sum

OrderItem.objects.aggregate(
    total_price=Sum(F('price') * 0.01 * (100-F('discount')))
)['total_price']

or for a specific order:

from django.db.models import F, Sum

myorder.order_item.aggregate(
    total_price=Sum(F('price') * 0.01 * (100-F('discount')))
)['total_price']

You likely want to do this per Order object:

from django.db.models import F, Sum

Order.objects.annotate(
    total_price=Sum(F('order_item__price') * 0.01 * (100-F('order_item__discount')))
)

The Order objects that arise from this will have an extra attribute .total_price that contains the total price.

Note: I would refrain from making a lot (all) fields NULL-able. It means that if fields are NULL, expressions and aggregates with these fields can be NULL as well, and often are if one of the included fields is NULL. Often NULL-able fields are rare, and used if it is valid to have missing information.

Note: The related_name=… [Django-doc] is the name of the manager to fetch the related objects in reverse. Therefore normally the related_name of a ForeignKey or ManyToManyField is plural, for example order_items instead of order_item.

Note: It might make more sense to work with a DecimalField [Django-doc] than with a FloatField [Django-doc]], since a decimal has fixed precision, and less rounding errors, Especially when working with financial data, that is more accurate.

Upvotes: 2

Related Questions