如何计算查询集中每个项目的两个字段的总和 [英] How to calculate the total of two fields for each item in the queryset

问题描述

假设我具有以下模型结构:

Assume I have the following model Structure:

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

出于数据一致性，我不想将 total_price 存储在我的数据库中，并希望像

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)

因此，我的数据库(PostgreSQL 9.1)将计算并返回它而不将其记录在数据库上，并且返回的查询集中的每个记录将包含 total_price ，这是 base_price 的总和，该记录的 commision .如果可以在计算字段上使用 filter ，那也很好.

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.

我该怎么做?

我想要类似于以下SQL的内容:

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

推荐答案

1..您可以使用

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

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

以上内容将为QuerySet中的每个项目添加 total_price 属性.但是，您将不能对其进行过滤-您将收到 FieldError:无法将关键字"total_price"解析为字段.

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..有一种未记录的方式可以使用

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)

这篇关于如何计算查询集中每个项目的两个字段的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！