Calculations

[Abdou KARAMBIZI]

class LoanInformation(models.Model):
    id = models.AutoField(primary_key=True)
    customer = models.ForeignKey(Customer,on_delete=models.CASCADE)
    amount_to_pay = models.IntegerField()
 
    def __str__ (self):
        return self.id

class Payments(models.Model):
    id = models.AutoField(primary_key = True)
    loan = models.ForeignKey(LoanInformation,on_delete=models.CASCADE)
    amount = models.IntegerField()
    paidon = models.DateField(auto_now=True)

    def __str__ (self):
        return self.id



I want to display amount_to_pay - sum of amount 

[Abdou KARAMBIZI]

I want to display the result of  "amount_to_pay - sum of amount"

[Thomas Couch]

Hi Abdou, have a look at aggregation (https://docs.djangoproject.com/en/4.2/topics/db/aggregation/)

In this case I think you'll want something like:

```

from django.db.models import Sum

# Where loan = the LoanInformation instance you're interested in

outstanding_balance = loan.amount_to_pay - loan.payments_set.aggregate(Sum("amount"))

```

[Thomas Couch]

Or, you can combine annotations and expressions to do something like this:

qs = LoanInformation.objects.annotate(outstanding=F("amount_to_pay") - Func(F("payments__amount"), function="SUM))

loan = qs.get(id=loan_id)

loan.outstanding

[Clement Idemudo]

in addition to what @Thomas said you need to first perform an aggregated sum on the payments model hence the need for ... loan.payments_set.aggregrate(models.Sum('amount'))
the above returns the sum of all payment related to your loan instance from which you can now subtract from loan.amount_to_pay

you can read this post for further clarity https://medium.com/@clemcy9/performing-aggregate-sum-in-postgresql-django-3472e5248bb3

[Abdou KARAMBIZI]

Hello Thomas 

I have tried but I got that error message

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/1b059aab-1b8e-4a8e-b9e3-d7cb9d1f5673n%40googlegroups.com.

[Thomas Couch]

Ah ok, looks like aggregate always returns a dictionary. This should work:

outstanding_balance = loan.amount_to_pay - loan.payments_set.aggregate(paid=Sum("amount"))['paid']

[Abdou KARAMBIZI]

Thanks Thomas, now it is working properly

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/9be67e3f-785b-4c39-ab6c-309fd33a8289n%40googlegroups.com.