Convert SQL query into Django ORM

user_4626788 0
Viewed 453

I have SQL query, I need to use in Django with ORM technique.

SELECT a.id, a.amount, SUM(b.amount)
FROM cashflow_statement a, cashflow_statement b
WHERE b.id <= a.id GROUP BY a.id
ORDER BY a.id

Edit: Django Model

class Statement(models.Model):
    amount = models.DecimalField(max_digits=10, decimal_places=2)
    date = models.DateField()

    def __str__(self):
        return self.title

Input Data

id, amount, date
1.    10   25/06/2020
2.    -10   25/06/2020
3.    40   25/06/2020
4.    10   25/06/2020
5.    -30   25/06/2020
6.    10   25/06/2020


Need Output:
id, amount, sum
1.   10.     10 
2.   -10.    0
3.    40.    40
4.   10.     50
5.   -30.    20
6.    10.    30
postgresql django-orm django-models django sql
2 Answers
x=Statement.objects.filter(date='your date')
sum=0
for i in x:
   sum+=x.amount

maybe i have mistake in code but i think you are looking for .filter() part. if you need to use multiple filters together it's somethink like this:

x=Statement.objects.filter(date='your date').filter(amount<10)
user_135571890

Currently Django does not support aggregates in subqueries, but with a workaround, we can "trick" the system in generating a correct query:

from django.db.models import OuterRef, Sum, Subquery, Value

Statement.objects.annotate(
    sum=Subquery(
        Statement.objects.filter(
            pk__lte=OuterRef('pk')
        ).values(x=Value(0)).order_by('x').annotate(
            sum=Sum('amount')
        ).values('sum')[:1]
    )
)
user_675790
Related