How do you calculate (easily) total and subtotal in Django?

Question

I’m wearing Django 1.7.8

I have the models.py

class SaleDetail(models.Model):
    quantity = models.PositiveSmallIntegerField(_('quantidade'))
    price_sale = models.DecimalField(

    def get_subtotal(self):
        return self.price_sale * self.quantity

    subtotal = property(get_subtotal)

./Manage.py shell

>>> from vendas_project.vendas.models import SaleDetail
>>> from django.db.models import Sum, F, FloatField
>>> q = SaleDetail.objects.values('price_sale', 'quantity').filter(sale=1)
>>> q.aggregate(Sum(F('price_sale') * F('quantity')), output_field=FloatField())

Generates the error:

field_list = aggregate.lookup.split(LOOKUP_SEP)
AttributeError:
    'ExpressionNode' object has no attribute 'split'

How you calculate subtotals and total in Django?

I need the result, example:

price_sale quantity subtotal

10.50      2        21.00

9.55       3        28.65

total = 49.65

Answer 1

In version 1.8 to generate subtotal you must use annotate and to generate the total you must use aggregate.

It is necessary to use Expressionwrapper when performing calculations with different types of values.

Example:

>>> from django.db.models import F, DecimalField, ExpressionWrapper
>>> q = SaleDetail.objects.filter(sale=1).values('price_sale', 'quantity')
>>> q_com_subtotal = q.annotate(subtotal=ExpressionWrapper(F('price_sale') * F('quantity')), output_field=DecimalField())
>>> q_com_subtotal[0].subtotal
21.0
>>> calculo_total = q_com_subtotal.aggregate(total=Sum('subtotal'))
>>> calculo_total.total
49.65

References:

• https://docs.djangoproject.com/en/1.8/ref/models/expressions/#query-Expressions
• https://docs.djangoproject.com/en/1.8/ref/models/expressions/#Django.db.models.Expressionwrapper

Answer 2

For Django 1.8 or later, see response from Orion.

Django version 1.7 or earlier cannot be used F as a parameter of annotate, aggregate or values (see ticket #14030), so that the only way to do this is by using pure SQL (via extra). I don’t know if it is possible to calculate total and subtotal in a single query. An example (tested in Sqlite) is:

>>> SaleDetail.objects.extra(select={
...   'subtotal':'round(price_sale * quantity, 2)',
... }).values('price_sale', 'quantity', 'subtotal')
[{'subtotal': 21.0, 'price_sale': Decimal('10.5'), 'quantity': 2},
 {'subtotal': 28.65, 'price_sale': Decimal('9.55'), 'quantity': 3}]

>>> SaleDetail.objects.extra(select={
...   'total':'round(sum(price_sale * quantity), 2)',
... }).values('total')
[{'total': 49.65}]