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?

you need something like this https://gist.github.com/shulcsm/a9899d39c7d7bb5ada5e in Postgres SQL this can be expressed as `ORDER BY wealth DESC NULLS LAST` — Anentropic, Mar 01 '18 at 19:24

score 5 · Accepted Answer · answered Mar 02 '18 at 04:51

5

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

answered Mar 02 '18 at 04:51

JPG

82,442
19
127
206

perfect, this is what I needed :) – Vidak Mar 02 '18 at 08:35

akpp · Answer 2 · 2019-05-17T15:30:40.867

1

In addition to JPG answer. You can also define Options.ordering attribute inside your view class:

class PersonViewSet(serializers.ModelViewSet):
    serializer_class = PersonSerializer
    ordering = [F('wealth').desc(nulls_last=True)]

edited May 17 '19 at 15:30

answered May 16 '19 at 12:41

akpp

706
10
12

Django REST ViewSet ordering for Null-able columns

2 Answers2