CODEWITHSUNDEEP
djangomodeldjango-managersaggregates
peterretief
peterretief

Reputation: 2067

Model manager queryset doubles the sum for sets of amounts

I want to return the sums of items and payments for an invoice using a model manager. It works correctly if there is only one record in the payment and item models, if I add more I can no longer chain the results. In the shell I tried the following

>>> Invoixe.objects.with_aggregates().last().tot
Decimal('60')
>>> Invoixe.objects.with_totals().last().tot
Decimal('30')

with_aggregates returns the wrong total(tot) value (The correct total is 30)

The SQL looks like this

SELECT
  "task_invoixe"."id",
  "task_invoixe"."name",
  (CAST(SUM("task_ixem"."amount") AS NUMERIC)) AS "tot",
  (CAST(SUM("task_paxment"."amount") AS NUMERIC)) AS "pay",
  (CAST(((CAST(SUM("task_ixem"."amount") AS NUMERIC)) - (CAST(SUM("task_paxment"."amount") AS NUMERIC))) AS NUMERIC)) AS "bal"
FROM "task_invoixe"
LEFT OUTER JOIN "task_ixem" ON ("task_invoixe"."id" = "task_ixem"."invoixe_id")
LEFT OUTER JOIN "task_paxment" ON ("task_invoixe"."id" = "task_paxment"."invoixe_id")
GROUP BY "task_invoixe"."id", "task_invoixe"."name"
ORDER BY "task_invoixe"."id" ASC
LIMIT 1

Here is the code ChatGPT believes this code should work I can create the same error by chaining managers like so

>>> Invoixe.objects.with_totals().with_payments().last().tot
Decimal('60')
>>> Invoixe.objects.with_totals().last().tot
Decimal('30')

Which also give the wrong amount

from django.db import models
from django.db.models import Sum, F

class InvoixeQueryset(models.QuerySet):

    def with_totals(self):
        return self.annotate(tot=Sum(F('ixem__amount')))

    def with_payments(self):
        return self.annotate(pay=Sum(F('paxment__amount')))

    def with_aggregates(self):
        return self.annotate(
            tot=Sum('ixem__amount'),
            pay=Sum('paxment__amount'),
        ).annotate(bal=F('tot') - F('pay'))

class InvoixeManager(models.Manager):
    def get_queryset(self):
        return InvoixeQueryset(self.model, using=self._db)
   
    def with_aggregates(self):
        return self.get_queryset().with_aggregates()

    def with_totals(self):
        return self.get_queryset().with_totals()
    
    def with_payments(self):
        return self.get_queryset().with_payments()
    
    def with_balance(self):
        return self.get_queryset().with_balance()


class Invoixe(models.Model):
     name = models.CharField(max_length=80, unique=True)
     objects = InvoixeManager()
     def __str__(self):
        return self.name

class Paxment(models.Model):
        amount = models.DecimalField(
            max_digits=20, decimal_places=2, default=0,
        )
        invoixe = models.ForeignKey('Invoixe', on_delete=models.CASCADE)
        def __str__(self):
            return str(self.amount)

class Ixem(models.Model):
        amount = models.DecimalField(
            max_digits=20, decimal_places=2, default=0,
        )
        invoixe = models.ForeignKey('Invoixe', on_delete=models.CASCADE)
        def __str__(self):
            return str(self.amount)

I would really appreciate if someone could guide me in finding the correct implementation. Thanks.

Upvotes: 0

Views: 30

Answers (1)

peterretief
peterretief

Reputation: 2067

I needed to force Django to use group-by using Case and When Also used one table to store debits and credits, overall it looks better.

    def with_aggregates(self):
        return self.annotate(
            debit_sum=Sum(Case(When(ledger__action="DR", then=F('ledger__amount')), output_field=DecimalField())),
            credit_sum=Sum(Case(When(ledger__action="CR", then=F('ledger__amount')), output_field=DecimalField()), 
            )
        ).annotate(
            balance=(F('debit_sum') - F('credit_sum'))
        )

Upvotes: 0

Related Questions