Search code examples
djangosql-order-bynullable

Django: order by position ignoring NULL

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?

Solution

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