How to calculate the total of two fields for each item in the queryset

Question

Assume I have the following model Structure:

class SomeModel(Model):
    base_price = DecimalField(....)
    commision = DecimalField(....)

I do not want to store total_price in my database for data consistency and wish to calculate it as base_price + commision like

SomeModel.Objects.all().xxxxxx(total_price=base_price + commision)

So my database (Postgresql 9.1) will calculate and return it without recording it on the database and each record in returning queryset will contain total_price that is sum of base_price and commision of that record. also it would be great if I can use filter on calculated field.

How can I do that?

I want something that is similar to following SQL:

select ("base_price" + "commision") as total_price, base_price, commision from some_table;

total_price | base_price | commision
-------------------------------------
    15.0    |   14.0     |    1.0
    22.0    |   20.0     |    2.0

score 2 · Accepted Answer · edited May 23 '17 at 11:57

1. You can use the extra() QuerySet method:

SomeModel.objects.extra(select={'total_price': 'base_price + commission'})

The above will add a total_price attribute to each item in the QuerySet. However, you will NOT be able to filter on it--you'll get a FieldError: Cannot resolve keyword 'total_price' into field.

2. There is an undocumented way to use annotate() to add a field that can be filtered on. In your case it'd be something like:

from django.db.models import Max

# with this method the total_price can be filtered on
SomeModel.objects.annotate(
    total_price=Max('base_price', field='base_price + commission')
).filter(total_price__lt=20)

How to calculate the total of two fields for each item in the queryset

1 Answers1

Linked