How can i implement coalesce query in django?

user_13811689 0
Viewed 1612

I'm trying to build an ecom website using django and djngo rest framework. My model Item contains price and sale_price column:

class Item(models.Model):
    price = models.FloatField()
    sale_price = models.FloatField(null=True,blank=True)

I want to filter them by max active price So in order to do that in sqlite i would write something like

SELECT * FROM item WHERE coalesce(sale_price,price) < maxPrice;

How can i do this in Django?

sqlite django python
1 Answers

Django has a Coalesce [Django-doc] function. You thus can filter for example on:

from django.db.models.functions import Coalesce

Item.objects.annotate(
    real_price=Coalesce('sale_price', 'price')
).filter(
    real_price__lt=maxPrice
)

or if you want the largest of the two, it is best to use:

from django.db.models.functions import Coalesce

Item.objects.annotate(
    real_price=Greatest(Coalesce('sale_price', 'price'), 'price')
).filter(
    real_price__lt=maxPrice
)

The way NULLs are handled in a greatest expression differ from database to database. For example SQLite will return NULL from the moment one of the parameters is NULL.

user_675790
Related