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.

Show your attempts with the subqueries... (Also you don't show enough information in your question wallet_transaction, transactions, should be some related models in that case you should show at least some of their relevant code and also show your model for which you write this manager) — Abdul Aziz Barkat
– Abdul Aziz Barkat, Commented Jun 19, 2021 at 13:42
I've added some wallet_transaction and transactions models and also added the query which I use the annotation and get the wrong sum. In my opinion let we ignore my attempts with subquery — Ahmed Samy
– Ahmed Samy, Commented Jun 19, 2021 at 14:13

Abdul Aziz Barkat · Accepted Answer · 2021-06-19 16:37:27Z

10

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

edited Jun 19, 2021 at 16:37

answered Jun 19, 2021 at 14:29

Abdul Aziz Barkat

22.3k9 gold badges107 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Ahmed Samy Over a year ago

I noticed that wallet_donation value always return the latest donation amount not the sum

Ahmed Samy Over a year ago

I've copied the with_donations_stats() as it it is working fine with the normal_donation I reviewed it with the database it is working but I noticed that the wallet_donation return the last amount added to the condition in the WalletTransaction table as I noted I am using the with_donations_stats() in the Condition.donations.filter(status=Condition.STATUS_PUBLISHED).with_donations_stats().order_by( '-id') no more what do you think ?

Abdul Aziz Barkat Over a year ago

@AhmedSamy can you add the output of print(queryset.query) and your exact queryset code to your question? (With queryset being the query you are making)

Ahmed Samy Over a year ago

I've added it to the question

Ahmed Samy Over a year ago

Let us continue this discussion in chat.

|

Collectives™ on Stack Overflow

Django multiple annotate with Sum get wrong answer

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related