Django使用Sum进行多重注释，得到错误的答案

Question

我试图将.annotate()与多个Sum()一起使用，但我计算错误。我读到我应该使用Subquery，但我没有完成它——也许我用错了方式(因为这是第一次(，或者它不能解决我的问题。

#managers.py
class DonationQuerySet(QuerySet):
def completed(self):
return self.with_donations_stats().filter(
amount__lte=F('total_donation'), deleted_at=None)
def not_completed(self):
return self.with_donations_stats().filter(
amount__gt=F('total_donation'), deleted_at=None)
def with_donations_stats(self):
return self.annotate(
wallet_donation=Coalesce(Sum('wallet_transaction__amount'), 0),
normal_donation=Coalesce(Sum('transactions__amount'), 0),
total_donation=F('wallet_donation') + F('normal_donation'))

class DonationManager(Manager):
def get_queryset(self):
return DonationQuerySet(self.model, using=self._db)
def completed(self):
return self.get_queryset().completed()
def not_completed(self):
return self.get_queryset().not_completed()
def with_donations_stats(self):
return self.get_queryset().with_donations_stats()

#models.py
class Transaction(models.Model):
def __str__(self):
return self.payment_id + ' - ' + self.status
condition = models.ForeignKey('condition.Condition', related_name='transactions',
on_delete=models.CASCADE) 

amount = models.IntegerField(null=False, blank=False)
class WalletTransaction(AbstractBaseModel):
condition = models.ForeignKey("condition.Condition", on_delete=models.SET_NULL, related_name='wallet_transaction', null=True)
amount = models.PositiveIntegerField()
def __str__(self):
return f"{self.id}"
class Condition(models.Model):
def __str__(self):
return str(self.id)  # .zfill(10)
STATUS_PUBLISHED = "Published"
STATUS_CHOICES = (
(STATUS_PUBLISHED, 'Published'),)
status = models.CharField(max_length=25, choices=STATUS_CHOICES, db_index=True)
donations = DonationManager()

在我看来，我在查询已发布的条件条件=

Condition.donations.filter(status=Condition.STATUS_PUBLISHED).with_donations_stats().order_by(
'-id')

UPDATE最终查询为SELECT "conditions"."id", "conditions"."user_id", "conditions"."association_id", "conditions"."nid", "conditions"."amount", "conditions"."donation", "conditions"."nid_type", "conditions"."first_name", "conditions"."father_name", "conditions"."last_name", "conditions"."nationality", "conditions"."gender", "conditions"."mobile", "conditions"."region", "conditions"."city", "conditions"."district", "conditions"."dob", "conditions"."introduction_file", "conditions"."disease_validation_file", "conditions"."medical_report_file", "conditions"."treatment_plan_file", "conditions"."cost_file", "conditions"."case_report_file", "conditions"."invoices_file", "conditions"."payment_file", "conditions"."generated_pdf_file", "conditions"."recovery_report_file", "conditions"."report_date", "conditions"."issued_place", "conditions"."specialization", "conditions"."disease_type", "conditions"."action_type", "conditions"."case_type", "conditions"."treatment_entity", "conditions"."accommodation_type", "conditions"."family_members", "conditions"."income_avg", "conditions"."medical_evaluation_status", "conditions"."researcher_opinion", "conditions"."rejection_reason", "conditions"."justification", "conditions"."insurance_company_name_ar", "conditions"."insurance_company_name_en", "conditions"."insurance_company_id", "conditions"."insurance_beneficiary_number", "conditions"."insurance_beneficiary_type", "conditions"."insurance_class", "conditions"."insurance_expiry", "conditions"."insurance_limit", "conditions"."insurance_policy", "conditions"."status", "conditions"."created_at", "conditions"."updated_at", "conditions"."published_at", "conditions"."deleted_at", "conditions"."image", "conditions"."last_donation_at", COALESCE((SELECT SUM(U0."amount") AS "amount_sum" FROM "wallets_wallettransaction" U0 WHERE U0."condition_id" = ("conditions"."id") GROUP BY U0."id" ORDER BY U0."id" DESC LIMIT 1), 0) AS "wallet_donation", COALESCE((SELECT SUM(U0."amount") AS "amount_sum" FROM "transactions" U0 WHERE U0."condition_id" = ("conditions"."id") GROUP BY U0."condition_id" LIMIT 1), 0) AS "normal_donation", (COALESCE((SELECT SUM(U0."amount") AS "amount_sum" FROM "wallets_wallettransaction" U0 WHERE U0."condition_id" = ("conditions"."id") GROUP BY U0."id" ORDER BY U0."id" DESC LIMIT 1), 0) + COALESCE((SELECT SUM(U0."amount") AS "amount_sum" FROM "transactions" U0 WHERE U0."condition_id" = ("conditions"."id") GROUP BY U0."condition_id" LIMIT 1), 0)) AS "total_donation" FROM "conditions" WHERE "conditions"."status" = Published ORDER BY "conditions"."id" DESC

注意：我在另一个视图中使用了相同的注释，得到了相同的结果。

Answer 1

您偶然发现了一个问题，即组合多个聚合[Django-docs]会产生错误的结果，因为使用了联接而不是子查询。

由于要对关系进行聚合Django会进行连接，而对多个关系进行聚合会有多个连接，因此结果当然是错误的。因此，我们需要使用子查询进行注释：

from django.db.models import OuterRef, Subquery

class DonationQuerySet(QuerySet):
...

def with_donations_stats(self):
# Subquery for wallet donation
# Added order_by because it appears you have some default ordering
wallet_donation = WalletTransaction.objects.filter(
condition=OuterRef('pk')
).order_by().values('condition').annotate(amount_sum=Sum('amount')).values('amount_sum')[:1]

# Subquery for normal donation
normal_donation = Transaction.objects.filter(
condition=OuterRef('pk')
).values('condition').annotate(amount_sum=Sum('amount')).values('amount_sum')[:1]

return self.annotate(
wallet_donation=Coalesce(Subquery(wallet_donation), 0),
normal_donation=Coalesce(Subquery(normal_donation), 0),
total_donation=F('wallet_donation') + F('normal_donation')
)