CODEWITHSUNDEEP
pythondjangotemplatesaggregateannotate
Gonzalo Dambra
Gonzalo Dambra

Reputation: 980

Django: Can't GROUP BY on a template table

I'm trying to show a table in the template, which shows transactions with their dates.

The query is:

resultado = Asiento.objects.filter(
    Empresa=request.session['codEmp'],
    codcta=cod_cta
).exclude(anulado='S').order_by(date)

But the user can check a checkbox which if set to true should show one row per date in the table.

Without group by:

+-------------------------------+-------+
|  date  | trans_in | trans_out | total |
+--------+----------+-----------+-------+
|2019/5/3| $5.000   |    $0     | $5.000|
+--------+----------+-----------+-------+
|2019/5/3|   $0     |   $2.500  |-$2.500|
+--------+----------+-----------+-------+
|2019/5/4|  $1.000  |    $0     |$1.000 |
+--------+----------+-----------+-------+

And what I'm trying to do is:

+-------------------------------+-------+
|  date  | trans_in | trans_out | total |
+--------+----------+-----------+-------+
|2019/5/3| $5.000   |  $2.500   |$2.500 |
+--------+----------+-----------+-------+
|2019/5/4|  $1.000  |    $0     |$1.000 |
+--------+----------+-----------+-------+

I've already tried by doing 

resultado = Asiento.objects.filter(
    Empresa=request.session['codEmp'],
    codcta=cod_cta
).exclude(anulado='S').order_by(date).values('date').annotate(
    dcount=Count('date')
)

Using annotate but it doesn't work! I really can't figure yet how to group by easier.

Later in the code I iterate with a for loop over resultado to add and substract the amount of money for each object in the queryset.

for asiento in resultado:
    add = add+asiento.add
    sub = sub+asiento.sub
    asiento.total = asiento.add-asiento.sub

total = add-sub

UPDATED:

I progressed in the problem so I'm adding more info in order to get this solved. The query I'm using is the following one:

resultado = Asiento.objects.filter(Empresa=request.session['codEmp'], codcta=cod_cta,
                                                               **query_kwargs).exclude(anulado='S')\
                                .order_by(orden).values('feasto').aggregate(dcount=Count('feasto'),
                                                                            debe=Sum('debe'), haber=Sum('haber'))

And it's working fine! The printing of resultado is:

{'dcount': 3, 'debe': Decimal('27602.23'), 'haber': Decimal('0.00')}

Which is correct because the query has three objects, all with the same date and it's adding debe (credit) properly.

But now I can't get how to show it properly in the template, since iterating with {% for asiento in resultado %} is printing the three rows that have the same date. And I only want one row per similar date.

Also I can't get these rows attrs that are out of the syntaxis .values().aggregate()... etc.

Upvotes: 0

Views: 378

Answers (1)

schillingt
schillingt

Reputation: 13731

I'm not entirely sure what you're trying to do. It's either to total up each date or to sum all totals. I've provided ways to do both.

from django.db.models import F, Sum, Count

resultado = Asiento.objects.filter(
    Empresa=request.session['codEmp'],
    codcta=cod_cta
).exclude(anulado='S').order_by(date).values('date').annotate(
    dcount=Count('date'),
    add_sum=Sum('add'),
    sub_sum=Sum('sub'),
).annotate(
    total=F('add_sum')+F('sub_sum')
)

This will give you each date with fields representing add_sum, sub_sum, and total. If you wanted to aggregate all the totals of all dates then you'd want to do then:

print(resultado.aggregate(total_all=Sum('total'))['total_all'])

Upvotes: 1

Related Questions