Why does nulls_last=False not put the nulls first in Django?

user_2077386 0
Viewed 1136

I'm finding that while nulls_last=True works, nulls_last=False doesn't. Example below is in a Django shell.

In [10]: [x.date for x in Model.objects.all().order_by(F('date').asc(nulls_last=True))]                                                                                                                               
Out[10]: 

[datetime.datetime(2020, 3, 10, 16, 58, 7, 768401, tzinfo=<UTC>),
 datetime.datetime(2020, 3, 10, 17, 4, 51, 601980, tzinfo=<UTC>),
 None,
 ]

[ins] In [11]: [x.last_run_created_at for x in Model.objects.all().order_by(F('date').asc(nulls_last=False))]                                                                                                                              
Out[11]: 
[datetime.datetime(2020, 3, 10, 16, 58, 7, 768401, tzinfo=<UTC>),
 datetime.datetime(2020, 3, 10, 17, 4, 51, 601980, tzinfo=<UTC>),
 None,
 ]

In [12]:

I've tried this with both desc() and asc().

django
1 Answers

The mistake is assuming that the opposite of nulls_last=True is nulls_last=False. It isn't.

nulls_last=True does the following to the query:

SELECT ... ORDER BY ... ASC NULLS LAST

Whereas nulls_last=False just means use the DB default:

SELECT ... ORDER BY ... ASC

What you want instead is to use nulls_first=True OR nulls_last=True to explicitly get the order you want.

This is mentioned in the docs, but perhaps not as explicitly as it could be:

Using F() to sort null values

Use F() and the nulls_first or nulls_last keyword argument to Expression.asc() or desc() to control the ordering of a field’s null values. By default, the ordering depends on your database.

user_20773860
Related