How to join on multiple column with a groupby in Django/Postgres-CodePudding

I have the following tables that I need to join on date and currency:

class Transaction(models.Model):
    description = models.CharField(max_length=100)
    date = models.DateField()
    amount = models.FloatField()
    currency = models.ForeignKey(Currency, on_delete=models.PROTECT)


class ExchangeRate(models):
    currency = models.ForeignKey(Currency, on_delete=models.PROTECT)
    rate = models.FloatField()
    date = models.DateField()

I need to join on both the date and currency columns, multiply the rate and the amount to give me the 'converted_amount'. I then need to group all the transactions by calendar month and sum up the 'converted_amount'.

Is this possible using the Django ORM or would I need to use SQL directly? If so, how do I go about doing this in Postgres?

CodePudding user response：

Assuming that the Dates in the "Exchange rates" table are independent from the dates in the Transactions table, so that for each Transaction, the corresponding "Exchange rates".Date is the latest date which is less or equal than the Transactions.Date, you can try this in Postgres :

In Postgres :

SELECT t.Currency
     , date_trunc('month', t.Date) AS period_of_time
     , sum(t.amount * er.Rate) AS sum_by_currency_by_period_of_time
  FROM Transactions AS t
 CROSS JOIN LATERAL
     ( SELECT DISTINCT ON (er.Currency) er.Rate
         FROM "Exchange rates" AS er
        WHERE er.Currency = t.Currency
          AND er.Date <= t.Date
        ORDER BY er.Date DESC
     ) AS er
 GROUP BY t.Currency, date_trunc('month', t.Date)

CodePudding user response：

Assuming that your Currency model has a symbol column (change to your needs) you can achieve this with the following Django statements:

from your.models import Transaction, ExchangeRate
from django.db.models.functions import ExtractMonth
from django.db.models import Sum, F, Subquery, OuterRef

rates = ExchangeRate.objects.filter(
    currency=OuterRef("currency"), date__lt=OuterRef("date")
).order_by("-date")

Transaction.objects.annotate(
    month=ExtractMonth("date"),
    rate=Subquery(rates.values("rate")[:1]),
    conversion=F("amount") * F("rate"),
).values("currency__symbol", "month").annotate(sum=Sum("conversion")).order_by(
    "currency", "month"
)

This will result in a list like:

{'currency__symbol': '$', 'month': 2, 'sum': 105.0},...

The subquery statement will annotate the last found exchange rate comparing the dates. Make sure that each transaction has an exchange rate (exchange rate date prior transaction date).