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

Question

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

Navigation

#1 by (4 votes)
#2 by (2 votes)

5

I'm using Django 1.7.8

I have 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())

Generate error:

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

How do you calculate subtotals and totals in Django?

I need the result, eg:

price_sale quantity subtotal

10.50      2        21.00

9.55       3        28.65

total = 49.65

django

asked by anonymous 29.05.2015 / 20:11

2 answers

4

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

You need 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:

link
link

29.05.2015 / 22:19

What is the best solution? GetById with id not existing in DB Make a website not run in any version of IE

score 2 · Accepted Answer

For Django 1.8 or later, see the Orion answer .

In version 1.7 of Django or earlier it is not possible to use F as parameter of annotate , aggregate or values (see ticket # 14030 ), so the only way to do this is to use pure SQL (via extra ). I do not 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}]