Django order by to just put all Null at the end

user_11103705 0
Viewed 71

There is the django order by method, here is an example:

Purchase.objects.all().order_by(F('price').desc(nulls_last=True))

This will order by the field price with the additional constraint to have all nulls as last.

I want something slightly different, take this example:

  • I have a queryset of Purchase with the following prices in THIS EXACT ORDER: [5, None, 13, None, 2]
  • I want to order by so the result is like this [5, 13, 2, None, None]

Basically I am looking for a way to simply move all instances where price=None at the end, not affecting the order of the rest of the elements in the queryset.

The method in the example at the top would also sort the rest of the elements in descendent order which I do no want.

Any help appreciated if this is even possible.

django-models django python
1 Answers

You can use Conditional Expressions to annotate a value which will indicate that the field is null for that row and then use that annotated value in the order_by clause:

from django.db.models import BooleanField, Case, Value, When


Purchase.objects.annotate(
    price_is_null=Case(
        When(price__isnull=True, then=Value(True, output_field=BooleanField())),
        default=Value(False, output_field=BooleanField()),
    )
).order_by('price_is_null', 'pk')

Here we have added pk to order_by so that the normal ordering is maintained, if you were using some other field for ordering add them after price_is_null in the call to order_by.

user_149918640
Related