Ignore null values in descending order using Django Rest Framework

Question

I am using Django for my website, and hence decided to use Django Rest Framework for building my REST APIs. For a particular model, i want to filter on a text field (using SearchFilter for that), filter on a few categorical fields (FilterBackend with a FilterSet defined) and be able to order data based on some fields (OrderingFilter for this).

class StatsAPI(generics.ListAPIView):
    model = Stats
    queryset = Stats.objects.all()
    serializer_class = StatsSerializer
    filter_backends = (filters.DjangoFilterBackend, filters.OrderingFilter, filters.SearchFilter)
    filter_class = StatsFilter
    pagination_class = StatsPagination
    ordering_fields = ('__all__')
    search_fields = ('display_name')

The issue i am facing is with my ordering fields as they also contain nulls. Ordering in ascending order works fine. However ordering in descending order (www.example.com/api/stats/?ordering=-appearance), pushes the null values to the top.

How do i ignore the null values when using descending order? The number of fields on which ordering can be performed are roughly 20 in number.

Tutelage

i am using Postgresql, which states The NULLS FIRST and NULLS LAST options can be used to determine whether nulls appear before or after non-null values in the sort ordering. By default, null values sort as if larger than any non-null value; that is, NULLS FIRST is the default for DESC order, and NULLS LAST otherwise. How do i pass on this value from django or django rest framework? – Tutelage

Answer 1

This is a slightly different solution -- rather than filtering null out, this replacement for filters.OrderingFilter just always makes sure they sort last:

class NullsAlwaysLastOrderingFilter(filters.OrderingFilter):
    """ Use Django 1.11 nulls_last feature to force nulls to bottom in all orderings. """
    def filter_queryset(self, request, queryset, view):
        ordering = self.get_ordering(request, queryset, view)

        if ordering:
            f_ordering = []
            for o in ordering:
                if not o:
                    continue
                if o[0] == '-':
                    f_ordering.append(F(o[1:]).desc(nulls_last=True))
                else:
                    f_ordering.append(F(o).asc(nulls_last=True))

            return queryset.order_by(*f_ordering)

        return queryset

Songstress

This is great. Using this code broke my field aliases*. I fixed this by using the get_ordering_value() method of OrderingFilter, for example: f_ordering.append(F(self.get_ordering_value(o[1:])).desc(nulls_last=True)) *I'm using django-filter 1.1.0 – Brock

Fantastic stuff. – Orlon

Also works with monkey patch OrderingFilter.filter_queryset = filter_queryset. This is useful if you want to apply "null last" everywhere. – Johnnie

Answer 2

You can custom your own OrderingFilter:

# Created by [email protected] at 2022/8/13
from django.db.models import F, OrderBy
from django_filters import rest_framework as filters


class MyOrderingFilter(filters.OrderingFilter):
    def get_ordering_value(self, param):
        value = super().get_ordering_value(param)
        return OrderBy(F(value.lstrip("-")), descending=value.startswith("-"), nulls_last=True)

Hypoderma

Answer 3

atrain's solution crashes when there is no ordering so I added a null check:

from django.db.models import OrderBy, F
from rest_framework.filters import OrderingFilter


class NullsLastOrderingFilter(OrderingFilter):

    def get_ordering(self, request, queryset, view):
        values = super().get_ordering(request, queryset, view)
        if not values:
            return values
        return (OrderBy(F(value.lstrip("-")), descending=value.startswith("-"), nulls_last=True) for value in values)

Also, you can replace OrderingFilter with this new filter from settings to use it project-wise:

'DEFAULT_FILTER_BACKENDS': [
    'django_filters.rest_framework.DjangoFilterBackend',
    'common.filters.NullsLastOrderingFilter',
],

Gaither

Answer 4

class NullLastFilter(filters.OrderingFilter):
    def get_ordering(self, request, queryset, view):
        ordering = super().get_ordering(request, queryset, view)

        if ordering:
            nulls_last_ordering = []
            for field in ordering:
                if field.startswith('-'):
                    nulls_last_ordering.append(F(field[1:]).desc(nulls_last=True))
                else:
                    nulls_last_ordering.append(F(field).asc(nulls_last=True))

            return nulls_last_ordering

        return ordering

Rifkin

Answer 5

Will ordering exclude the null values, assuming your field name is stats here you can do as follows :

Stats.objects.exclude(stats__isnull=True).exclude(stats__exact='')

Derbyshire

i have 20 fields in the Stats model, on which i can perform the ordering.. so how would i handle that? Also i do not want to exclude the nulls.. i just dont want them at the top when using descending order.. – Tutelage

I don't think there is a inbuild function for that, but writing a list comprehension on the filtered object list would work, but time complexity will increase. – Derbyshire

Fair enough, i think i am going to set default = 0 instead. – Tutelage

Answer 6

BaiJiFeiLong's solution almost worked for me. With some tweaks, this ended up doing the trick:

from django.db.models import F, OrderBy
from rest_framework.filters import OrderingFilter

class NullsLastOrderingFilter(OrderingFilter):

def get_ordering(self, request, queryset, view):
    values = super().get_ordering(request, queryset, view)
    return (OrderBy(F(value.lstrip("-")), descending=value.startswith("-"), nulls_last=True) for value in values)

Sharonsharona

This solution throws the error "'generator' object is not subscriptable" with DRF 3.14 and python 3.11. I had to return a list instead of a generator. – Jurisprudence

Answer 7

I found out that rest_framework.pagination.CursorPagination does not support OrderBy objects coming from the OrderingFilter (only str). This means subclassing OrderingFilter like suggested here will crash, so the change needs to happen at a lower level.

To monkeypatch nulls_last=True behaviour globally (for any ordering string passed to QuerySet.order_by), add this snippet in a file that gets imported on startup (e.g. settings.py):

from django.db.models import F, OrderBy
from django.db.models.sql.query import Query

def add_ordering(self, *ordering):
    """Ensure nulls always come last when doing a QuerySet.order_by."""
    ordering = [
        OrderBy(
            F(value.lstrip("-")),
            descending=value.startswith("-"),
            nulls_last=True,
        )
        if isinstance(value, str)
        else value
        for value in ordering
    ]

    return self.add_ordering_orig(*ordering)

# monkeypatch
setattr(Query, "add_ordering_orig", Query.add_ordering)
setattr(Query, "add_ordering", add_ordering)

Catherinacatherine