Groupping and aggregating on django model related records

Question

I'm learning Django, and I'm facing a little issue:

I have a model which has related rows, and I'd like to get the sum of a column of the related rows grouping by the value of a second column.

The model's I'm working with are the following:

class Integrante(models.Model):
    nombre = models.CharField(max_length=80, db_index=True)

class EstadoCuenta(models.Model):
    num_edc = models.PositiveIntegerField(validators=[MinValueValidator(1),])
    fecha_edc = models.DateField(null=True, db_index=True)

class Movimiento(models.Model):
    TIPOS_MOVIMIENTO = (
        # Choices for column 'tipo'
    )
    estado_cuenta = models.ForeignKey(EstadoCuenta, on_delete=models.CASCADE)
    integrante = models.ForeignKey(Integrante, on_delete=models.CASCADE)
    fecha = models.DateField(db_index=True)
    tipo = models.SmallIntegerField(db_index=True, choices=TIPOS_MOVIMIENTO)
    descripcion = models.CharField(max_length=200, blank=True)
    importe = models.DecimalField(max_digits=8, decimal_places=2)

    class Meta:
        ordering = ['integrante', 'fecha', 'tipo', 'pk']

I've tried the following:

edc = EstadoCuenta.objects.filter(grupo__nombre_grupo='A group').latest()
edc.movimiento_set.values('integrante').annotate(Sum('importe'))

However, I'm getting the following result:

for x in edc.movimiento_set.values('integrante').annotate(Sum('importe')):
    print(x)

# {'integrante': 28, 'importe__sum': Decimal('-20.00')}
# {'integrante': 28, 'importe__sum': Decimal('23.00')}
# {'integrante': 28, 'importe__sum': Decimal('9.60')}
# {'integrante': 28, 'importe__sum': Decimal('20.00')}
# {'integrante': 28, 'importe__sum': Decimal('-0.60')}
# {'integrante': 24, 'importe__sum': Decimal('96.00')}
# {'integrante': 24, 'importe__sum': Decimal('28.80')}
# {'integrante': 24, 'importe__sum': Decimal('48.00')}
# {'integrante': 24, 'importe__sum': Decimal('28.80')}
# {'integrante': 24, 'importe__sum': Decimal('96.00')}
# {'integrante': 24, 'importe__sum': Decimal('48.00')}
# {'integrante': 24, 'importe__sum': Decimal('288.00')}
# {'integrante': 24, 'importe__sum': Decimal('144.00')}
# {'integrante': 24, 'importe__sum': Decimal('19.20')}
# {'integrante': 24, 'importe__sum': Decimal('-510.00')}
# {'integrante': 24, 'importe__sum': Decimal('48.00')}
# {'integrante': 24, 'importe__sum': Decimal('48.00')}
# {'integrante': 24, 'importe__sum': Decimal('48.00')}
# {'integrante': 24, 'importe__sum': Decimal('48.00')}
# {'integrante': 24, 'importe__sum': Decimal('48.00')}
# {'integrante': 24, 'importe__sum': Decimal('48.00')}
# {'integrante': 24, 'importe__sum': Decimal('35.00')}
# {'integrante': 24, 'importe__sum': Decimal('235.00')}
# {'integrante': 24, 'importe__sum': Decimal('-0.80')}
# ...

The result I'm trying to get is something like this (done directly in the database server):

select integrante_id, sum(importe) 
from core_movimiento 
where estado_cuenta_id=233 
group by integrante_id;

-- | integrante_id | sum(importe) |
-- +---------------+--------------+
-- |            24 |       844.00 |
-- |            25 |         0.00 |
-- |            26 |         0.00 |
-- |            27 |      -232.00 |
-- |            28 |        32.00 |
-- |            29 |         0.00 |
-- |            30 |        20.00 |

Can you point me in the right directions?

I'm using Python 3.6 and Django 1.11

Bonus

I would also like to get the sum of importe separated in columns, depending of the value of column tipo. In SQL, I can get that like this:

select integrante_id
     , sum(case when tipo=1 then importe else 0 end) as pagos
     , sum(case when tipo in (11, 12) then importe else 0 end) as ventas
     , sum(case when tipo in (70, 71) then importe else 0 end) as paquetes
     , sum(case when tipo=99 then importe else 0 end) as ajustes 
     , sum(importe) as total
from core_movimiento 
where estado_cuenta_id = 233 
group by integrante_id;

-- | integrante_id | pagos   | ventas  | paquetes | ajustes | total   |
-- +---------------+---------+---------+----------+---------+---------+
-- |            24 | -510.00 | 1084.80 |   270.00 |   -0.80 |  844.00 |
-- |            25 |  -35.00 |    0.00 |    35.00 |    0.00 |    0.00 |
-- |            26 |  -20.00 |    0.00 |    20.00 |    0.00 |    0.00 |
-- |            27 | -422.00 |  190.00 |     0.00 |    0.00 | -232.00 |
-- |            28 |  -20.00 |   32.60 |    20.00 |   -0.60 |   32.00 |
-- |            29 | -200.00 |    0.00 |   200.00 |    0.00 |    0.00 |
-- |            30 |    0.00 |    0.00 |    20.00 |    0.00 |   20.00 |

Any ideas?

Answer 1

for your second question add new answer for more readable, since django 1.8 has conditional-expressions

from django.db.models import Sum, When, Case, IntegerField, F

edc.movimiento_set.values('integrante'
    ).annotate(total=Sum('importe')
    ).annotate(pagos=Sum(
        Case(
            When(tipo=1, then=F('importe')),
            default=0,
            output_field=IntegerField()
            )
        )
    ).annotate(ventas=Sum(
        Case(
            When(tipo__in=(11, 12), then=F('importe')),
            default=0,
            output_field=IntegerField()
            )
        )
    ).annotate(paquetes=Sum(
        Case(
            When(tipo__in=(70, 71), then=F('importe')),
            default=0,
            output_field=IntegerField()
            )
        )
    ).annotate(ajustes=Sum(
        Case(
            When(tipo=99, then=F('importe')),
            default=0,
            output_field=IntegerField()
            )
        )
    ).order_by()

hope all right.

Answer 2

your first just need add empty order by:

edc.movimiento_set.values('integrante').annotate(Sum('importe')).order_by()
#                                                              ^^^^^^^^^^

by default django add group by for ordering fields ordering-or-order-by

for bonus i think, will update the answer later