25

I have two models called User and Transaction . Here i want to get the all the users with total sum of the transaction amount where status is success.

I have tried with subquery but i am not getting how to annotate the aggregate of the subquery with conditions

class User(models.Model):
  name = models.CharField(max_length=128)

class Transaction(models.Model):
  user = models.ForeignKey(User)
  status = models.CharField(choices=(("success", "Success"),("failed", "Failed")))
   amount = models.DecimalField(max_digits=10, decimal_places=2)

subquery = Transaction.objects.filter(status="success", user=OuterRef('pk')).aggregate(total_spent = Coalesce(Sum('amount'), 0))

query = User.objects.annotate(total_spent=Subquery(subquery:how to do here ?)).order_by(how to order here by total_spent)
coder
  • 451
  • 2
  • 7
  • 13

5 Answers5

54

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.

Brad Martsberger
  • 1,747
  • 13
  • 7
  • May I ask, what if the `amount` is also an annotate field? It seems that the `__amount` doesn't work for subquery field like this. – Dingkun Liu Feb 26 '20 at 05:09
  • @DingkunLiu, the subquery is correct. there is not `__amount` in it. if `amount` is an annotated field of `Transaction` object the subquery will not change – Nwawel A Iroume Dec 03 '20 at 10:36
  • Don't you need a `.order_by('user__pk') ` after the first values in order to group by? – Drugo Dec 13 '22 at 22:57
12

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

Slava
  • 1,559
  • 1
  • 12
  • 17
  • If you have the weird "group by" error add .order_by to the end of subquery. https://newbedev.com/django-conditional-subquery-aggregate – Artem Dumanov Oct 01 '21 at 19:38
  • So helpful, thank you! I had to make a little adjustment to change "GROUP BY transaction.id" to "GROUP BY user.id", which is to add an initial call to `values("user_id")`. – bjt38 Aug 17 '22 at 10:52
0

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),),
    )
Charlestone
  • 1,248
  • 1
  • 13
  • 27
-1

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'))
chirag soni
  • 936
  • 10
  • 20
-2

For using the subquery, use this:

query=User.objects.annotate(total_spent=Subquery(subquery.values("user")[:1])).order_by("total_spent")
Molinge Jr
  • 90
  • 4