CODEWITHSUNDEEP
pythondjangodjango-models
Alejandro Jourdan
Alejandro Jourdan

Reputation: 325

Django sum based on foreignkey relationship (3 models involved)

I have the following question: I have 3 models: Contrato, Movimiento and MovimientoDato, I need to generate a queryset that throws me the SUM of the field importe of MovimientoDato for each Contrato, MovimientoDato is linked to Movimiento and Movimiento is linked to Contrato through foreign keys In the resulting queryset I need all the Contrato fields plus a field that we can call balance that adds up the amounts of MovimientoDato.importe related to each Contract using Movimiento as the bridge to make SUM Group, is this possible?

Here are the models:

class Movimiento(models.Model):
    fecha = models.DateField(default=timezone.now)
    contrato = models.ForeignKey(Contrato, related_name='rn_contratos',
    on_delete=models.CASCADE)
    OPCIONES = (
        (1, 'Abono'),
        (2, 'Cargo'),
    )
    tipomovimiento = models.IntegerField(choices=OPCIONES, default=1)
    formapago = models.ForeignKey(
        FormaPago, on_delete=models.CASCADE, null=True, blank=True)
    nota = models.CharField(max_length=255, blank=True)
    archivo = models.FileField(upload_to='images/', blank=True)
    user = models.ForeignKey(User, on_delete=models.CASCADE)

class MovimientoDato(models.Model):
    movimiento = models.ForeignKey(
    Movimiento, related_name='partidas', on_delete=models.CASCADE)
    categoria = models.ForeignKey(Categoria, on_delete=models.CASCADE)
    concepto = models.CharField(max_length=150, verbose_name="Concepto")
    importe = models.DecimalField(max_digits=12, decimal_places=2, default=0)

class Contrato(models.Model):
    propiedad = models.ForeignKey(Propiedad, on_delete=models.CASCADE)
    inquilino = models.ForeignKey(Inquilino, on_delete=models.CASCADE)
    fechainicio = models.DateField(default=datetime.now)
    fechafinal = models.DateField(default=datetime.now)
    renta_check = models.BooleanField(default=True)
    renta = models.DecimalField(max_digits=15, decimal_places=2, default=0)
    mantenimiento_check = models.BooleanField(default=True)
    mantenimiento = models.DecimalField(
        max_digits=15, decimal_places=2, default=0)
    agua_check = models.BooleanField(default=True)
    agua = models.DecimalField(max_digits=15, decimal_places=2, default=0)
    electricidad_check = models.BooleanField(default=True)
    electricidad = models.DecimalField(
        max_digits=15, decimal_places=2, default=0)
    deposito = models.DecimalField(max_digits=15, decimal_places=2, default=0)
    status = models.BooleanField(default=True)
    user = models.ForeignKey(User, on_delete=models.CASCADE)

I've searched how to do it with itertools, Q(), F() but I can't get it to work

Thank you very much

Upvotes: 1

Views: 96

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476574

Yes, you can .annotate(..) [Django-doc] the Contrato queryset with:

from django.db.models import Sum

Contrato.objects.annotate(
    balance=Sum('rn_contratos__partidas__importe')
)

The Contrato objects that arise from this queryset will have an extra attribute .balance that is the sum of the .importes all the related MovimientoDato (through the Movimiento model).

Upvotes: 1

Related Questions