Django - Aggregate of an Aggregation

Question

I am working with DRF and am having issues defining my queryset for use in my view class. Suppose I have three models like so:

class ExchangeRate(...):
    date = models.DateField(...)
    rate = models.DecimalField(...)
    from_currency = models.CharField(...)
    to_currency = models.CharField(...)

class Transaction(...):
    amount = models.DecimalField(...)
    currency = models.CharField(...)
    group = models.ForeignKey("TransactionGroup", ...)

class TransactionGroup(...):
    ...

I want to create a queryset on the TransactionGroup level with the following:

• 1. for each Transaction in the transaction group, add an annotated field converted_amount that multiplies the amount by the rate on the ExchangeRate instance where the currency matches the to_currency respectively
• 2. then sum up the converted_amount for each Transaction and set that on the TransactionGroup level as the annotated field converted_amount_sum

An example json response for TransactionGroup using this desired queryset:

[
  {
    "id": 1,
    "converted_amount_sum": 5000,
    "transactions": [
      {
        "id": 1,
        "amount": 1000,
        "converted_amount": 500,
        "currency": "USD",
      },
      {
        "id": 2,
        "amount": 5000,
        "converted_amount": 4500,
        "currency": "EUR",
      },
  },
  ...
]

My attempt at building a queryset (is there a way to construct this on the TransactionGroup level?):

from django.db.models import F

annotated_transactions = Transaction.objects.annotate(
    converted_amount = F("amount") * exchange_rate.rate     # <-- simplifying here
).values(
    "transaction_group"
).annotate(
    amount=Sum("converted_amount"),
)

I can get the annotations to work properly on the Transaction model - but trying to then sum them up again on the TransactionGroup level throws the error:

FieldError: Cannot compute Sum('converted_amount'), `converted_amount` is an aggregate

For added context - I want to be able to sort and filter the TransactionGroups by the convreted_amount_sum without having to do additional db lookups / operations.

Answer 1

You can work with a Subquery expression ^[Django-doc]:

from datetime import date

from django.db.models import F, OuterRef, Subquery

FILTER_DATE = date.today()

annotated_transactions = (
    Transaction.objects.values('transaction_group')
    .annotate(
        amount=Sum(
            F('converted_amount')
            * Subquery(
                ExchangeRate.objects.filter(
                    from_currency=OuterRef('currency'),
                    to_currency='USD',
                    date=FILTER_DATE,
                ).values('rate')[:1]
            )
        ),
    )
    .order_by('transaction_group')
)

But the modeling probably just complicates things. You could make a model Currency and work with links to make the expression a lot easier:

class Currency(models.Model):
    code = models.CharField(max_length=4, primary_key=True)


class ExchangeRate(models.Model):
    date = models.DateField()
    rate = models.DecimalField()
    from_currency = models.ForeignKey(
        Currency,
        db_column='from_currency',
        related_name='exchange_from',
        on_delete=models.PROTECT,
    )
    to_currency = models.ForeignKey(
        Currency,
        db_column='to_currency',
        related_name='exchange_to',
        on_delete=models.PROTECT,
    )


class Transaction(models.Model):
    amount = models.DecimalField()
    currency = models.ForeignKey(
        Currency,
        db_column='currency',
        related_name='transactions',
        on_delete=models.PROTECT,
    )
    group = models.ForeignKey('TransactionGroup', on_delete=models.PROTECT)

Then we can work with:

from datetime import date

from django.db.models import F, Sum

FILTER_DATE = date.today()
TransactionGroup.objects.filter(
    transaction__currency__exchange_from__exchange_to_id='USD',
    transaction__currency__exchange_from__date=FILTER_DATE,
).annotate(
    total_amount=Sum(
        F('transaction__amount') * F('transaction__currency__exchange_from__rate')
    )
)

It will require adding a "dummy exchange rate", preferrably for every currency such that USD to USD on any date is 1.0. While it is possible to do it withouth such dummy, it will complicate the matters.

We thus JOIN the transaction balance with the exchange rates for a given date, and thus then sum up the converted values.