Django - Filter based on nested sum of related values

Question

I have 3 Django models like this

Request is a donation request, RequestItem denotes how many instances of the name item are needed, Donation denotes how many donations have been done for the connected item

class Request:
    pass


class RequestItem:
    name
    request = ForeignKey(Request)
    needed = IntegerField()


class Donation:
    item = ForeignKey(RequestItem)
    donated = IntegerField()

A RequestItem a is said to be complete if

a.needed == Sum(donation.donated for each donation in a.donation_set.all()

A Request r is said to be complete if

For every RequestItem ri in r.request_item_set.all(); ri is complete

I need to filter out the requests which are complete/aren't complete.

It's not clear what you are asking for. You want to get the number of donations required to fulfil all related RequestItems? — Iain Shelvington
– Iain Shelvington, Commented Feb 12, 2021 at 22:22
No, I need to get the Request items for which all the RequestItems have their requirements fulfilled. To simplify, let's say Request A is connected to 2 RequestItems B and C. B is connected to a Donation D; and C is connected to Donations E and F. A will be said to be fulfilled/complete, if B.needed=D.donated and C.needed=E.donated+F.donated — Rahul Singh
– Rahul Singh, Commented Feb 13, 2021 at 2:48

Timofey Chernousov · Accepted Answer · 2021-02-13 03:20:55Z

1

If you are sure sum of 'donated' will never exceed value of 'needed' for every request item, you could do this way:

from django.db.models import Sum, F
Request.objects.annotate(needed=Sum('requestitem__needed', distinct=True), donated=Sum('requestitem__donation__donated')).filter(donated__gte=F('needed'))

Here sum of all donations per request is compared against sum of all 'needed' values for the same request. If former is greater or equivalent, request could be considered as completed

edited Feb 13, 2021 at 3:20

answered Feb 13, 2021 at 3:14

Timofey Chernousov

1,2948 silver badges12 bronze badges

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

1 Comment

Iain Shelvington Over a year ago

You need to pass distinct=True to the needed Sum otherwise multiple related Donations will cause repeated sums

Collectives™ on Stack Overflow

Django - Filter based on nested sum of related values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related