Django subquery with aggregate
I have two models called
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)
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:
It can't be evaluated before it is used
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' )
.values causes the correct group by. The second
.values causes selecting the one value that you want.
#28296 (Add support for aggregation through subqueries) – Django, I think with Subquery we can get SQL similar to one you have provided, with this code # Get amount of departments with GROUP BY� contains_aggregate¶ Tells Django that this expression contains an aggregate and that a GROUP BY clause needs to be added to the query. contains_over_clause¶ Tells Django that this expression contains a Window expression. It’s used, for example, to disallow window function expressions in queries that modify data. filterable¶
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'))
Django subquery with aggregate, count() because we have to group the rows on state_id and then aggregate them. The subquery count logic should be in its own method. Note� This allows the aggregation of annotated values by calculating the aggregate over partitions based on the outer query model (in the GROUP BY clause), then annotating that data to every row in the subquery queryset. The subquery can then use the aggregated data from the first row returned and ignore the other rows.
For using the subquery, use this:
Django conditional Subquery aggregate, I want to share very import Django ORM query in business logic which You can get basic ORM query like Join, OrderBy, Aggregation(Sum, Count Create a new column with subquery result and filter the annotated column. aggregate() is a terminal clause for a QuerySet that, when invoked, returns a dictionary of name-value pairs. The name is an identifier for the aggregate value; the value is the computed aggregate. The name is automatically generated from the name of the field and the aggregate function.
The Dramatic Benefits of Django Subqueries and Annotations, This fails because the Django ORM doesn't support aggregate in Subquery . See https://code.djangoproject.com/ticket/28296. I tried some solutions posted in� In the official Django documentation there is no info about using Django ORM update() and annotate() functions to update all rows in a queryset by using an annotated value. We are going to show a way to update an annotated Django queryset using only Django ORM subquery() without using extra() functions or SQL code.
[Django] 5 ORM queries you should know! | by chrisjune, queryset = Model. objects. annotate (total = Subquery (Model. objects. aggregate (Sum ('field')), percent = F ('field') / F ('total'),) But that won't be possible until #28296 gets fixed. In the mean time you can work around the issue by performing two queries.
Sum multiple values over a queryset including not included rows , I get the following SQL generated for the aggregate (notice that both alias used are the same in the SQL, whereas they are not in the queryset): subquery It works
- May I ask, what if the
amountis also an annotate field? It seems that the
__amountdoesn't work for subquery field like this.
- The question asked for the SUM but not the first instance in the queryset.