Django : Order by position ignoring NULL

Question

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 1

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