Convert SQL query into Django ORM

Question

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

@WillemVanOnsem Added models and output what I need, Above Sql giving relevant output. — Neeraj Kumar
– Neeraj Kumar, Commented May 25, 2020 at 12:34

willeM_ Van Onsem · Accepted Answer · 2020-05-25 13:04:13Z

1

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]
    )
)

edited May 25, 2020 at 13:04

answered May 25, 2020 at 12:51

willeM_ Van Onsem

482k33 gold badges483 silver badges624 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Neeraj Kumar Over a year ago

ProgrammingError at /statement/ non-integer constant in ORDER BY LINE 1: ..."cashflow_statement"."id") GROUP BY NULL ORDER BY NULL ASC ... Getting error. If I removed, values(x=Value(None)).order_by('x') after this, getting same value of sum in each Statement. so it's not updating according to statements

willeM_ Van Onsem Over a year ago

@NeerajKumar: this is PostgreSQL?

willeM_ Van Onsem Over a year ago

@NeerajKumar: with PostgreSQL, you probably need to pass an int, and removing values will of course yield the same value, since then we GROUP BY the primary key of the Statement object.

Neeraj Kumar Over a year ago

Yes, It's Postgresql

melihuyelik · Accepted Answer · 2020-05-25 12:49:28Z

0

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)

answered May 25, 2020 at 12:49

melihuyelik

463 bronze badges

Collectives™ on Stack Overflow

Convert SQL query into Django ORM

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related