Django calculate sum of column after multiplying each row with a different value

user_815859 0
Viewed 172

I have three Django models defined as:

class User(models.Model):
    name=models.CharField
    income=models.DecimalField
    expenditure=models.DecimalField
    group=models.DecimalField
    country = models.ForeignKey(Country)

class Country(models.Model):
    name=models.CharField
    currency = models.ForeignKey(Currency)

class Currency(models.Model):
    name=models.CharField
    value=models.DecimalField

Users can be from different countries. Each country has a currency whose value is the exchange rate with USD.

I am trying to calculate the sum of all the column after multiplying each value with the respective currency exchange rate. For example, for 3 Users:

John:2:10:1:2
Jame:10:11:1:1
Mark:5:11:1:1

Country
1:USA:1
2:UK:2

Currency:
1:USD:1
2:GBP:1.5

I would like to print the sum after calculating

income: (2*1.5) + (10*1) + (5*1) 
expenditure: (10*1.5) + (11*1) + (11*1)

Final output:

income: 18
expenditure: 37

I am thinking something like this might work but I could not quite get it:

user=User.objects.filter(group=1).aggregate((Sum('income')*(country_currency_value),(Sum('expenditure')*(country_currency_value))

Any help is much appreciated

django python
1 Answers

You should use F() expressions to multiply fields together, then use the __ operator in field names to reference foreign keys.

User.objects
  .filter(group=1)
  .aggregate(
    total_income=Sum(F('income') * F('country__currency__value')),
    total_expenditure=Sum(F('expenditure') * F('country__currency__value')),
  )

will return:

{'total_income': Decimal('18'), 'total_expenditure': Decimal('37')}
user_14084350
Related