Menu
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!
680
In Django queryset we have __isnull to check if a value is null. Here were filtering all the values from MyModel model, which have an alias = Null. exclude allows you to pass multiple arguments, it will the then check for every argument. If your query is still more complex, you can use Django's Q Objects.
When we read the data from the model, Django returns queryset. Django has order_by method to sort the queryset in ascending and descending order. You can order the queryset on any field.
If you are just doing two simple filter operations, then you're correct that order doesn't matter, but be careful. There are examples of when the order of your queryset methods do matter: https://docs.djangoproject.com/en/dev/topics/db/aggregation/#order-of-annotate-and-filter-clauses.
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')
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
38
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
