djangodjango-ormdjango-aggregation

django ORM problem - Annotate and aggregate return inconsistent values


I found a setup where aggregate() and annotate() do NOT return the same values... I am really confused.

I have the following models:

class Invoice(models.Model):
    pass

class InvoiceItem(models.Model):
    invoice = models.ForeignKey(Invoice, related_name="items")
    amount = models.PositiveIntegerField(null=False, blank=False, default=1)
    price = models.DecimalField(max_digits=10, decimal_places=2, null=False, blank=False, default=0)
    begin = models.DateField(null=True, blank=True)

When I want to get the total revenue over a timespan, I have the following query:

revenue = Invoice.objects.annotate(
               first_date=Min('items__begin')).filter(
               first_date__range=[start_date, end_date]).aggregate(
               revenue=Sum(F('items__amount') * F('items__price'),               
                           output_field=FloatField()))['revenue']

I tried to do the same with annotate() just to verify the results:

revenue_list = Invoice.objects.annotate(
                  first_date=Min('items__begin')).filter(
                  first_date__range=[start_date, end_date]).annotate(
                  revenue=Sum(F('items__amount') * F('items__price'),               
                              output_field=FloatField()))

When I now loop over the elements and manually sum up revenue I get a different value than revenue.

Any ideas? Unfortunately after an aggregation you cannot check the raw query anymore...

Thanks!

PS: I checked the values manually with SQL. annotate is right...


Solution

  • Ok, I found a solution. distinct() or values() did not work but to move the multiplication to a second annotate:

    revenue =  Invoice.objects.annotate(
                   first_date=Min('items__begin')).filter(
                   first_date__range=[start_date, end_date]).annotate(
                   revenue_invoice=Sum(F('items__amount') * F('items__price'),
                                       output_field=FloatField())).aggregate(
                   revenue=Sum('revenue_invoice', output_field=FloatField()))['revenue']