[Django]-Distinct values with multiple Sum aggregations on reverse relation

by

4👍

✅

You can try to use django Subquery expression. In your case your query look like this:

from django.db.models import Sum, OuterRef, Subquery

qs = Client.filter('project__date__year=2017').annotate(
    isum=Subquery(Project.objects.filter(client=OuterRef('pk')).values('client_id').annotate(sum=Sum('intoffer__amount')).values('sum')[:1]),
    esum=Subquery(Project.objects.filter(client=OuterRef('pk')).values('client_id').annotate(sum=Sum('extoffer__amount')).values('sum')[:1])
)

Note that it can be slow on large tables, in this case probably better to use RawSQL.

👤Dima Kudosh

Leave a comment