[Fixed]-Django order items by two fields, but ignoring them if they're zero

by

12👍

✅

You could use the extra() QuerySet method to create an extra field in your query using the COALESCE function in SQL, which returns the first non-NULL value it’s passed.

Product.objects.extra(select={"current_price":"COALESCE(sale_price, price)"}, order_by=["-current_price"])

You have to put your order_by in the extra() call as the extra manual field “doesn’t really exist” as far as the rest of the ORM is concerned, but the syntax is the same as normal Django order_by()s.

See the extra() documentation here: http://docs.djangoproject.com/en/1.3/ref/models/querysets/#extra

👤ojno

21👍

If you stumble upon this requirement and happen to be using Django 1.8 and higher, you can use django.db.models.functions.Coalesce for a slightly nicer, cross db-engine, solution:

from django.db.models.functions import Coalesce

Product.objects.annotate(
    current_price=Coalesce('sale_price', 'price')
).order_by('-current_price')
👤tutuDajuju

Leave a comment