Django multiple annotate with Sum get wrong answer

Question

I'm trying to use .annotate() with multiple Sum() But i got wrong calculations. I've read that I should use Subquery but I didn't get it done with it maybe I use it in wrong way (because it is first time) or it doesn't solve my issue.

#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()

and in my view i was querying for published conditions conditions =

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

UPDATE the final query is 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

Note: and I'm using the same annotation in another view resulting the same thing.

Answer 1

You have stumbled upon the problem that Combining multiple aggregations [Django docs] will yield the wrong results because joins are used instead of subqueries.

Since to make aggregations over relations Django makes joins and with aggregation on multiple relations there would be multiple joins the results of course are wrong. Therefore we need to annotate using subqueries:

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