带有聚合的Django子查询 [英] Django subquery with aggregate

问题描述

我有两个模型，分别是 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），您需要了解有关子查询的这两件事：

1. 在使用之前进行评估




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

因此，您不能在子查询上调用 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:

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.

这篇关于带有聚合的Django子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！