summation of multiple rows to check if value is less than referenced model in django

user_10602634 0
Viewed 86

I tried to implement this SQL query using Django, where multiple payments under the same fee in possible and I have to get the payments that are lower than the fee amount.

I have managed to get total payments under same fee id using

Fee.objects.get(pk=self.pk).payment_set.aggregate(Sum('amount'))

but cant find a way to compare it with payment_fee.amount

SQL I tried to implement:

SELECT
    payment_fee.id,
    payment_fee.amount ,
    SUM(payment_payment.amount) 
    as `su`
FROM
    payment_payment
inner join payment_fee on
    fee_id = payment_fee.id
GROUP By
    fee_id
HAVING
    (su)<payment_fee.amount

django models:

class Fee(models.Model):
    amount = models.DecimalField(max_digits=10, decimal_places=2, default=0)

class Payment(models.Model):
    fee = models.ForeignKey(Fee, on_delete=models.CASCADE, related_name='fee_payment')
    amount = models.DecimalField(max_digits=10, decimal_places=2, default=0)
django-queryset orm sqlite django sql
1 Answers

Something like the following should work. First annotate each Fee with the total of all related Payment amounts, then filter the Fees where this annotation is less than the Fee amount

from djago.db.models import Sum, F

Fee.objects.annotate(
    total_payments=Sum('fee_payment__amount')
).filter(
    total_payments__lt=F('amount')
)
user_5485620
Related