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 (assumes the exchange_rate.rate = 0.5:

[
  {
    "id": 1,
    "converted_amount_sum": 2000,
    "transactions": [
      {
        "id": 1,
        "amount": 1000,
        "converted_amount": 500,
        "currency": "USD",
      },
      {
        "id": 2,
        "amount": 3000,
        "converted_amount": 1500,
        "currency": "USD",
      },
  },
  ...
]

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

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"),
)

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.