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...
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']