Django Model Queryset Calculation

Question

For example

class Rice(models.Model):
    quantity = models.DecimalField(...)

Lets say:

rices = Rice.objects.all()
for i in rices:
    i.quantity

This gives output as : 5 7 9 10

Now what i want to achieve is a total_quantity till that object is reached what my desired output should be : 5 12 21 31

Logic: First object total_quantity till that object is calculated is 0+5 then, next object total_quantity is 0+5+7 then 0+5+7+9 and so on.

And this should be done using (queryset) annotate/aggregate or any other db models functions. (If not possible then any other way)

If the order changes the the output should also change.

Srijan113 · Accepted Answer · 2023-07-16T12:52:07.510

1

Anyway, I found a way for doing it using queryset only (If, any one is interested).

from django.db.models import Sum, Window

from .models import Rice

# Annotate the cumulative sum of quantities based on a specific order
objects = Rice.objects.annotate(
    cumulative_quantity=Window(
        expression=Sum('quantity'),
        order_by='id'
    )
).order_by('id')


for i in objects:
    print(i.cumulative_quantity)

Output: 5 12 21 31

edited Jul 16 '23 at 12:52

answered Jul 16 '23 at 08:44

Srijan113

89
8

1

Better append a `.order_by('id')` to the queryset so that ordering of window matches with query. Otherwise, the order is not guaranteed and thus the result may not be as expected. – Ezon Zhao Jul 16 '23 at 10:47

score 0 · Answer 2 · answered Jul 14 '23 at 15:10

"Any other way" is trivial

If you literally just want to annotate the objects with a running total:

quantity_so_far = 0
rices = Rice.objects.all().order_by( ...) # whatever ordering you want
for i in rices:
    quantity_so_far += i.quantity
    setattr( i, 'running_total', quantity_so_far )
    # or just i.running_total = quantity_so_far

I don't know how to accomplish this with the ORM, and I'd need to be told of a very good reason to even think about that (if it was my job to do so!)

My first thought was you were asking how to select objects until the total quantity equals or exceeds some desired quantity. that's not what the question says, but ...

selected = []
quantity_so_far = 0
rices = Rice.objects.all().order_by( ...) # whatever ordering you want
for i in rices:
    selected.append(i)
    quantity_so_far += i.quantity
    if quantity_so_far >= desired_quantity:
        break
else:
    # handle the error situation, where there aren't enough
    # to satisfy desired_quantity
    raise ...

After this code, selected is the list of objects which can satisfy or more than satisfy desired_quantity.

Django Model Queryset Calculation

2 Answers2