Django order items by two fields, but ignoring them if they're zero

Question

I have the following model (greatly simplified for the purposes of this question):

class Product(models.Model):
    price = models.DecimalField(max_digits=8, decimal_places=2)
    sale_price = models.DecimalField(max_digits=10, blank=True, null=True, decimal_places=2)

For the majority of products, price will be filled but sale_price will not be. So, I can order products by price like so:

Product.objects.order_by('price')
Product.objects.order_by('-price')

However, some products will have a sale_price, and I can't find a way to order these neatly so that the sale price interleaves with the normal price. If I try ordering by both fields:

Product.objects.order_by('sale_price','price')

...then all the products that aren't on sale appear together, followed by all that are, instead of interleaving the prices.

Does this make sense? Does anyone have a way to solve this?

Thanks!

Answer 1

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')