django - Annotate a sum of two fields multiplied

Question

I have three models, simplified for the example:

class Customer(models.Model):
    email = models.CharField(max_length=128)

class Order(models.Model):
    customer = models.ForeignKey(Customer)
    order_status = models.CharField(blank=True, max_length=256)

class Lineitem(models.Model):
    order = models.ForeignKey(Order)
    quantity = models.IntegerField(blank=True)
    price = models.DecimalField(max_digits=6, decimal_places=2)

I want to query the customers (possibly with a filter) and annotate the total they have spent (that is, the sum over (price * quantity)

I have tried:
Customer.objects.filter(something).annotate(total_spent=Sum(F('order__lineitem__quantity') * F('order__lineitem__price')))

It would appear that Sum() cannot be used with F() expressions. Is there another way to do this?

question from:https://stackoverflow.com/questions/808201/annotate-a-sum-of-two-fields-multiplied

Answer 1

Maybe you don't need this answer now, but if you read the documentation about Sum expression , you need to declare the output_field, like this:

Customer.objects.filter(something)
                .annotate(total_spent=Sum(
                    F('order__lineitem__quantity') * 
                    F('order__lineitem__price'),   
                    output_field=models.FloatField()
                ))