Django ORM - add rows to the result until the SUM('column') reaches to a given number

Question

I have a model created using Django ORM -

class Orders:
    units = models.IntegerField(null=False, blank=False)
    sell_price_per_unit = models.FloatField(null=True, blank=True)
    created_by = models.ForeignKey(User, on_delete=models.PROTECT, null=False, blank=False)
    order_status = models.ForeignKey(OrderStatus, on_delete=models.PROTECT, null=False, blank=False)
    property = models.ForeignKey(Property, on_delete=models.PROTECT, null=False, blank=False)
    order_type = models.ForeignKey(Property, on_delete=models.PROTECT, null=False, blank=False)
    ...
    ...

I want to write a query to select_for_update() and the rows should be selected until the Sum('sell_price_per_unit') reaches a given number. Additionally I have to apply certain filter and exclude certain rows based on the condition. filter() & exclude() were pretty straight forward so I already took care of it but I have no idea of how to select rows until the Sum('sell_price_per_unit') reaches to a given number.

queryset = (
            Orders.objects.select_for_update()
            .exclude(created_by=created_by)
            .values(
                "id",
                "property",
                "units",
                "created_by",
                "sell_price_per_unit",
                "order_status",
            )
            .filter(
                property=order_obj.property,
                order_type__name=SELL,
                order_status__in=[PARTIALLY_COMPLETED[1], OPEN[1]],
                sell_price_per_unit__lte=order_price,
            )
            .order_by("sell_price_per_unit", "created_at")
        )

Is it possible to accomplish this?

You might look for the equivalent of SQL `having` clause, thus [this post](https://stackoverflow.com/questions/13344182/how-to-implement-the-having-clause-in-sqlite-django-orm) might help you — Christophe, Jan 31 '22 at 10:29

score 1 · Accepted Answer · answered Jan 31 '22 at 10:36

Here the SQL solution of similar problem. I'm not sure, if it's possible to write it in DjangoORM.

So, you can use rawSQL to query records.

But if you have reasonable amount of records you can simply filter it on application side

queryset = (
    Orders.objects.select_for_update()
    .exclude(created_by=created_by)
    .values(
        "id",
        "property",
        "units",
        "created_by",
        "sell_price_per_unit",
        "order_status",
    )
    .filter(
        property=order_obj.property,
        order_type__name=SELL,
        order_status__in=[PARTIALLY_COMPLETED[1], OPEN[1]],
        sell_price_per_unit__lte=order_price,
    )
    .order_by("sell_price_per_unit", "created_at")
)

current_sum = 0

orders = []
for order in queryset:
    current_sum += order.sell_price_per_unit
    if current_sum > threshold:
        break

# Do stuff with orders
...

I am putting a lock on each row I am fetching. If I fetch too many rows and put a lock on it then other services will be on halt until the current transaction is finished. Also, in this case fetching limited number of rows will reduce the possibility of deadlock. Hence, this solution won't actually work for me. — Jeet Patel, Feb 01 '22 at 08:28
@JeetPatel you can select orders without `select_for_update` and filter by current_sum. Then you can do `orders = Orders.objects.select_for_update().filter(order__in=orders).all()`. So, you put a lock only on necessary rows — Yevhen Bondar, Feb 01 '22 at 09:05

Django ORM - add rows to the result until the SUM('column') reaches to a given number

1 Answers1