CODEWITHSUNDEEP
django
coder
coder

Reputation: 561

Django subquery with aggregate

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)

Upvotes: 33

Views: 25367

Answers (5)

Slava
Slava

Reputation: 1997

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

Upvotes: 14

Charlestone
Charlestone

Reputation: 1260

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),),
    )

Upvotes: 0

Brad Martsberger
Brad Martsberger

Reputation: 1967

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.

Upvotes: 68

chirag soni
chirag soni

Reputation: 1026

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'))

Upvotes: -1

Molinge Jr
Molinge Jr

Reputation: 100

For using the subquery, use this:

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

Upvotes: -2

Related Questions