Filter Queries which sum of their amount field are greater or lesser than a number-CodePudding

let's say this is my model :

class Item(models.Model):
    user = models.ForeignKey(User, on_delete=models.DO_NOTHING)
    price = models.DecimalField(max_digits=23, decimal_places=8, null=True, blank=True)
    amount = models.DecimalField(max_digits=23, decimal_places=8)

i'm trying to get all the records which sum of their amount will be lesser than any integer i give. for example from 20 records that exists , it returns first 5 records which sum of their amount is 1000 . it is like having these values : 100,400,300,100,100 . the sum is 1000 so it returns them as queryset. it is possible to implement it with loops but i'm trying to handle it with django orm .

can anyone help me with this ?

CodePudding user response：

You can use Window functions to run a cumulative sum per row, ordered by the primary key like this:

from django.db.models import Sum, Window


Item.objects.annotate(
    cumulative_sum=Window(
        Sum('price'),
        order_by=F('id').asc()
    )
).filter(cumulative_sum__lte=1000)

This will then return the first few item instances with prices that add up to less than or equal to 1000.

CodePudding user response：

Gets all the items, Create empty item_list, Loop through all items, If item amount is under 1000 append to item_list.

    items = Item.objects.all()
    item_list = []
    for item in items:
        if item.amount < 1000:
          item_list.append(item)

CodePudding user response：

There is a way to do this using aggregate function Sum from Django.

from django.db.models import Sum
amount_sum = Item.objects.filter(amount__lt=1000).aggregate(amount_sum=Sum("amount"))["amount_sum"]

However, The above Django query will have a equivalent SQL query as:

SELECT SUM("app_Item"."amount") AS "amount_sum" 
FROM "app_Item" 
WHERE "app_Item"."amount" < 1000; args=(Decimal('1000'),)