CODEWITHSUNDEEP
djangodjango-models
VoidArray
VoidArray

Reputation: 205

Alternative nullif in Django ORM

Use Postgres as db and Django 1.9

I have some model with field 'price'. 'Price' blank=True. On ListView, I get query set. Next, I want to sort by price with price=0 at end.

How I can write in SQL it: 'ORDER BY NULLIF('price', 0) NULLS LAST'

How write it on Django ORM? Or on rawsql?

Upvotes: 3

Views: 2443

Answers (2)

VoidArray
VoidArray

Reputation: 205

Ok. I found alternative. Write own NullIf with django func.

from django.db.models import Func

class NullIf(Func):
    template = 'NULLIF(%(expressions)s, 0)'

And use it for queryset: 

queryset.annotate(new_price=NullIf('price')).order_by('new_price')

Edit : Django 2.2 and above have this implemented out of the box. The equivalent code will be

from django.db.models.functions import NullIf
from django.db.models import Value
queryset.annotate(new_price=NullIf('price', Value(0)).order_by('new_price')

Upvotes: 8

quetzaluz
quetzaluz

Reputation: 1131

You can still ORDER BY PRICE NULLS LAST if in your select you select the price as SELECT NULLIF('price', 0). That way you get the ordering you want, but the data is returned in the way you want. In django ORM you would select the price with annotate eg TableName.objects.annotate(price=NullIf('price', 0) and for the order by NULLS LAST and for the order by I'd follow the recommendations here Django: Adding "NULLS LAST" to query

Otherwise you could also ORDER BY NULLIF('price', 0) DESC but that will reorder the other numeric values. You can also obviously exclude null prices from the query entirely if you don't require them.

Upvotes: 0

Related Questions