Filter `calculated field` in Django
In SQL filter your fields that fit to your formula is piece of cake since you were taught to use WHERE
clause at the first hour of your database101. However, I don’t want to go back date to early day since I have ORM in my hand. Here is my problem
from django.contrib.postgres.fields import DecimalRangeField
from django.db import models
class Premium(models.Model):
percentage = models.DecimalField(max_digits=10, decimal_places=2)
sum_insured = DecimalRangeField() # Insurer accepts this range
def __str__(self):
return f"{self.percentage} | {self.sum_insured}"
Please ignore sum_insured
range field for now. I am about to show you filter
According to premium
it has formula as follows
premium = percentage * given_sum_insured
Therefore I have to make premium
column first. Using annotate
query = Premium.objects.annotate(premium=F('percentage') / 100.0 * given_sum_insured)
At this point I can use ORM operator like this is a real field
filtered_qs = query.filter(premium__gte=125)
In my repository it has following topics including filter on calculated field
1. Django REST Framework
2. graphene-django
3. django_filters
4. TestCase of them