如何在 Django 模型的一对多相关字段上执行条件聚合？

Question

给定以下模型模式:

class Transaction(models.Model):
wallet = models.ForeignKey(related_name="transactions")
amount = models.DecimalField()  # must be positive value
type = models.CharField(choices=[("deposit", "deposit"), ("withdrawal", "withdrawal")]
class Wallet(models.Model):
pass

获取数据库中每个钱包记录的余额的最有效的Django ORM查询是什么?

current_balance = sum of "amounts", where type="deposits" - sum of "amounts", where type="withdrawal"

让我们假设我们不能更改给定表/记录的列或字段，并且amount必须保持为正值。

想法吗?

Answer 1

也许我们可以这样做:

from django.db.models import Sum
Wallet.objects.annotate(
balance=Sum('transactions__amount',filter=Q(transactions__type='deposit')) -
Sum('transactions__amount',filter=Q(transactions__type='withdrawal'))
)

但我建议在提款的情况下，把金额记为负数，这样我们就可以简单地把交易加起来:

# when using negative amounts for withdrawals
from django.db.models import Sum
Wallet.objects.annotate(
balance=Sum('transactions__amount')
)