Django : Order by position ignoring NULL

django sql-order-by nullable
user650108 picture user650108 · Mar 8, 2011 · Viewed 9.8k times · Source

I have a problem with django queryset ordering.

My model contains a field named position (a PositiveSmallIntegerField), which I'd like to used to order query results.

I use order_by('position'), which works great.

Problem : my position field is nullable (null=True, blank=True), because I don't wan't to specify a position for every 50000 instances of my model :(

When some instances have a NULL "position", order_by returns them in the top of the list : I'd like them to be at the end...

In RAW SQL, I used to write things like "IF(position IS NULL or position='', 1, 0)" (see http://www.shawnolson.net/a/730/mysql-sort-order-with-null.html) : is it possible to get the same result using Django, without writing raw SQL ?

Thanks very much !

Answer

Sergey Golovchenko picture Sergey Golovchenko · Mar 8, 2011

You can use the annotate() from django agrregation to do the trick:

items = Item.objects.all().annotate(null_position=Count('position')).order_by('-null_position', 'position')

Related questions

Django Query using .order_by() and .latest()

I have a model: class MyModel(models.Model): creation_date = models.DateTimeField(auto_now_add = True, editable=False) class Meta: get_latest_by = 'creation_date' I had a query in my view that did the following: instances = MyModel.objects.all().…

Read More
Sorting related items in a Django template

Is it possible to sort a set of related items in a DJango template? That is: this code (with HTML tags omitted for clarity): {% for event in eventsCollection %} {{ event.location }} {% for attendee in event.attendee_set.all %} {{ attendee.first_name }} {{ …

Read More
What is the default order of a list returned from a Django filter call?

Short Question What is the default order of a list returned from a Django filter call when connected to a PostgreSQL database? Background By my own admission, I had made a poor assumption at the application layer in that the …

Read More