[Django]-How can i implement coalesce query in django?

by

2👍

✅

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.

👤willeM_ Van Onsem

Leave a comment