[SOLVED] How to perform conditional aggregation on a Django model's one to many related field?

How to perform conditional aggregation on a Django model's one to many related field?

Given the following model schemas:

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

What is the most efficient Django ORM query to obtain the balances of every wallet record in the database?

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

Let's assume we cannot change the columns or fields of the given tables / records and amount must remain a positive value.

Thoughts?

Solution

Probably we can work with:

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'))
)

But I would advise to make the amount negative in case of a withdrawal, in that case we can simply sum up the transactions with:

# when using negative amounts for withdrawals

from django.db.models import Sum

Wallet.objects.annotate(
    balance=Sum('transactions__amount')
)