[Django]-Django subquery with aggregate

This is made a lot easier with the django-sql-utils package.

from django.db.models import Sum,
from sql_util.utils import SubqueryAggregate

User.objects.annotate(
    total_spend=SubqueryAggregate('transaction__amount',
                                  filter=Q(status='success'),
                                  aggregate=Sum)
)

If you want to do it the long way (without django-sql-utils), you need to know these two things about the subquery:

1. It can’t be evaluated before it is used

2. It can only return a single record with a single column

So, you can’t call aggregate on the subquery, because this evaluates the subquery immediately. Instead you have to annotate the value. You also have to group by the outer ref value, otherwise you’ll just annotate each Transaction independently.

subquery = Transaction.objects.filter(
        status='success', user=OuterRef('pk')
    ).values(
        'user__pk'
    ).annotate(
        total_spend=Sum('amount')
    ).values(
        'total_spend'
    )

The first .values causes the correct group by. The second .values causes selecting the one value that you want.

You can do it like this:

subquery = Transaction.objects.filter(
    status="success", user=OuterRef('pk')
).annotate(
    total_spent = Coalesce(Func('amount', function='Sum'), Decimal(0))
).values('total_spent')

query = User.objects.annotate(
    total_spent=Subquery(subquery)
).order_by('total_spent')

The more details on this method you can see in this answer: https://stackoverflow.com/a/69020732/10567223

The suggested solution didn’t work for me when there was ordering set on the model.

class InstallmentReservation(models.Model):
    class Meta:
        ordering = ['id']

I needed to clear the ordering to make it work again.

    subquery.query.clear_ordering(True)

Whole code example – a method on a queryset – hope it helps

def with_installment_reservations_amounts(self):
    """
    Sum of initial amount of active installment reservations annotated in _installment_reservations_initial_amount
    Sum of principal amount of active installment reservations annotated in _installment_reservations_amount
    `.values('customer')` in subquery is used to properly sum values. See https://stackoverflow.com/questions/55925437/django-subquery-with-aggregate for more details.
    also this does not work when there is an ordering set on a model for some reason, so we need to clear it.
    """

    reservation_query = InstallmentReservation.objects.filter(customer_id=OuterRef('pk')).active().values('customer')
    reservation_query.query.clear_ordering(True)

    return self.annotate(
        _installment_reservations_amount=Coalesce(Subquery(reservation_query.annotate(sum=Sum('amount_principal')).values('sum')[:1]), Decimal(0),),
        _installment_reservations_initial_amount=Coalesce(Subquery(reservation_query.annotate(sum=Sum('initial_installment_amount')).values('sum')[:1]), Decimal(0),),
    )

You can hit this query:

from django.db.models import Avg, Count, Min, Sum

User.objects.filter(status="success").annotate(total_amount=Sum('transaction__amount'))

For using the subquery, use this:

query=User.objects.annotate(total_spent=Subquery(subquery.values("user")[:1])).order_by("total_spent")