Django REST ViewSet ordering for Null-able columns

Question

Consider a table Person:

| name | wealth |
|------| ------ |
|Abby  | 12     |
|Ben   | Null   |
|Carl  | 5      |
|Diane | Null   |

We want to sort the rows by wealth, descending, i.e. to get (Abby, Carl, Ben, Diane), but Django's order_by function sorts them by Null first:

class PersonViewSet(serializers.ModelViewSet):
        serializer_class = PersonSerializer
        queryset = Person.objects.all().order_by('-wealth)

gives (Ben, Diane, Abby, Carl), i.e. it first lists the Null values then sorts by wealth.

---

I tried redefining the get_queryset method:

class PersonViewSet(serializers.ModelViewSet):
    serializer_class = PersonSerializer

    def get_queryset():
        invalid_entries = Person.objects.filter(wealth=None)
        valid_entries = Person.objects.all().difference(invalid_entries).order_by('-wealth')
        return valid_entries.union(invalid_entries)

This does return the desired behavior, (Abby, Carl, Ben, Diane) but messes up the detail view, and gives the get() returned multiple values error.

---

Is there a way to get the desired behavior, by customizing the ordering functionality, or modifying get_queryset only for the list view?

Answer 1

from the changelog of django 1.11

Added the nulls_first and nulls_last parameters to Expression.asc() and desc() to control the ordering of null values.

So, If you are using django>=1.11, you can Expression.desc() method to sort your fields as below,

from django.db.models import F

queryset = Person.objects.all().order_by(F('wealth').desc(nulls_last=True))