Django: Can't GROUP BY on a template table

Question

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.

score 1 · Accepted Answer · answered Mar 23 '19 at 13:48

1

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'])

answered Mar 23 '19 at 13:48

schillingt

13,493
2
32
34

I appreciate your answer @schillingt. I can't get why when I use .values('date').annotate(dcount=Count('date')) and I print the variable with the results, it shows a couple of results with the same value in 'date' field and "'dcount': 1" instead of showing only one and a different ammount value in dcount. I can't get why. Maybe because it's a date field? – Gonzalo Dambra Mar 23 '19 at 14:09
Your answer was right! The issue with my code was that I was including an ".order_by" in the query, which resulted in some non grouped similar dates. I just removed the "order_by" and it worked perfect! – Gonzalo Dambra Mar 25 '19 at 14:12
Awesome, I'm glad you figured it out. – schillingt Mar 25 '19 at 14:56

Django: Can't GROUP BY on a template table

1 Answers1