CODEWITHSUNDEEP
pythondjangodjango-querysetaggregation
angardi
angardi

Reputation: 375

Django: How can I add an aggregated field to a queryset based on data from the row and data from another Model?

I have a Django App with the following models:

CURRENCY_CHOICES = (('USD', 'US Dollars'), ('EUR', 'Euro'))

class ExchangeRate(models.Model):
    currency = models.CharField(max_length=3, default='USD', choices=CURRENCY_CHOICES)
    rate = models.FloatField()
    exchange_date = models.DateField()


class Donation(models.Model):
    donation_date = models.DateField()
    donor = models.CharField(max_length=250)
    amount = models.FloatField()
    currency = models.CharField(max_length=3, default='USD', choices=CURRENCY_CHOICES)

I also have a form I use to filter donations based on some criteria:

class DonationFilterForm(forms.Form)
    min_amount = models.FloatField(required=False)
    max_amount = models.FloatField(required=False)

The min_amount and max_amount fields will always represent values in US Dollars.

I need to be able to filter a queryset based on min_amount and max_amount, but for that all the amounts must be in USD. To convert the donation amount to USD I need to multiply by the ExchangeRate of the donation currency and date.

The only way I found of doing this so far is by iterating the dict(queryset) and adding a new value called usd_amount, but that may offer very poor performance in the future.

Reading Django documentation, it seems the same thing can be done using aggregation, but so far I haven't been able to create the right logic that would give me same result.

Upvotes: 1

Views: 856

Answers (1)

angardi
angardi

Reputation: 375

I knew I had to use annotate to solve this, but I didn't know exactly how because it involved getting data from an unrelated Model.

Upon further investigation I found what I needed in the Django Documentation. I needed to use the Subquery and the OuterRef expressions to get the values from the outer queryset so I could filter the inner queryset.

The final solution looks like this:

# Prepare the filter with dynamic fields using OuterRef
rates = ExchangeRate.objects.filter(exchange_date=OuterRef('date'), currency='EUR')

# Get the exchange rate for every donation made in Euros
qs = Donation.objects.filter(currency='EUR').annotate(exchange_rate=Subquery(rates.values('rate')[:1]))
# Get the equivalent amount in USD
qs = qs.annotate(usd_amount=F('amount') * F('exchange_rate'))

So, finally, I could filter the resulting queryset like so:

final_qs = qs.filter(usd_amount__gte=min_amount, usd_amount__lte=max_amount)

Upvotes: 2

Related Questions