How can i get the greater of two different Decimal fields for a model using a Django query?
For example if i have a model Month with fields called income_actual and income_projected, how do i return the greater value?
I have previously used the MySQL GREATEST() function to do it but i cant work out how to do it in Django.
Have you looked into using the extra method?
Month.objects.extra(select={'max_income': 'greatest(income_actual, income_projected)'})
EDIT:
You're not going to be able to use that through the ORM without writing raw SQL. Although you could use some python magic:
sum(month['max_income'] for month in Month.objects.extra(select={'max_income': 'greatest(income_actual, income_projected)'}).values('max_income'))
You may use Django's Database Function Greatest.
For example you may use such a query:
>>> from django.db.models.functions import Greatest
>>> months = Month.objects.annotate(greatest_income=Greatest('income_actual', 'income_projected').all()
# now you can access the greatest value using something like this:
>>> months[0].greatest_income
@drewman already gave you the version when you want to use strictly SQL. I would do things a bit differently, and add a property to the model which dynamically calculates the correct version.
class model(models.Model):
....
@property
def income(self):
return max(self.income_actual, self.income_real)
