CODEWITHSUNDEEP
pythondjangodjango-modelsdjango-rest-frameworkdjango-queryset
atabak hooshangi
atabak hooshangi

Reputation: 144

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

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 ?

Upvotes: 2

Views: 764

Answers (3)

Brian Destura
Brian Destura

Reputation: 12068

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.

Upvotes: 1

vikas soni
vikas soni

Reputation: 598

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'),)

Upvotes: 1

Ryan Thomas
Ryan Thomas

Reputation: 536

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)

Upvotes: 0

Related Questions