How to make a conditional query in Django?

Question

I am trying to filter by a calculated field, where the calculation depends on the value of other fields.

I'm trying to filter by sales_price (the calculated field), where sales_price is defined like below pseudocode

if discount is NULL                                                             
    sales_price = price                                                         
else                                                                            
    sales_price = price - price*discount/100 

The end goal is to filter sales_price by range:

filter(sales_price__range=(price_min, price_max))                                   

Here is my model:

class Product(models.Model):                                                
  price = models.IntegerField()                                             
  discount = models.IntegerField(blank=True, null=True)                                                                             

Answer 1

I'll just point you in the right direction:

Use F expressions in a conditional expression with When and Case

You want to sort by a value that depends on other values, so let's use a F Expression (because sales_price depends on other fields) in a conditional expression (because the final expression depends on whether discount is NULL or not)

First we construct a sales_price value that depends on discount and price, and annotate our query with it:

from django.db.models import When, Case, F, IntegerField

Product.objects.annotate(
     sales_price=Case(
         When(discount__isnull=True, then=F('price')),
         When(discount__isnull=False, then=(F('price') - (F('discount') * F('price')) / 100)),
         output_field=IntegerField(),
     )
)

Now with this, you have included a sales_price that you can filter with:

   Product.objects.annotate(...).filter(sales_price__range=(price_min, price_max)