带有聚合的Django子查询

本文介绍了带有聚合的Django子查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有两个模型，分别是 User 和 Transaction 。在这里，我想获得状态为成功的所有用户的交易金额总和。

我尝试了子查询，但是我没有得到如何注释具有条件的子查询的总和

class User（models.Model）： name = models.CharField（max_length = 128） class Transaction（models.Model）： user = models.ForeignKey（User） status = models.CharField（choices =（（（ success， Success ），（失败，失败））））数量= models.DecimalField（max_digits = 10，decimal_places = 2） 子查询= Transaction.objects.filter（status = 成功，用户= OuterRef（'pk'））。aggregate（total_spent = Coalesce（Sum（'amount'），0）） 查询= User.objects.annotate（total_spent = Subquery（ subquery：如何在这里进行操作？））。order_by（如何在total_spent处进行排序）

解决方案

使用 django-sql-utils 包。

from django.db.models import Sum，$来自sql_util.utils的b $ b导入SubqueryAggregate User.objects.annotate（ total_spend = SubqueryAggregate（'transaction__amount'， filter = Q（status ='success'）， 合计=总和））

如果您想长期这样做方式（没有django-sql-utils），您需要了解有关子查询的这两件事：

在使用之前进行评估

它只能返回具有单个列的单个记录

因此，您不能在子查询上调用 aggregate ，因为这会立即评估子查询。相反，您必须注释该值。您还必须按外部ref值进行分组，否则，您将单独注释每个事务。

子查询= Transaction.objects .filter（ status ='success'，user = OuterRef（'pk'））.values（'user__pk'）.annotate（ total_spend = Sum（'amount'））.values（'total_spend'）

第一个 .values 导致正确的分组依据。第二个 .values 导致选择所需的一个值。

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)

解决方案

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

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