Django: Natural Sort QuerySet

Question

I am looking for a way to naturally sort Django's QuerySets. I found a similar question, but it did not focus on QuerySets. Instead they are doing it directly in Python.

So here is my problem. Let's say I have this model:

class Item(models.Model):
    signature = models.CharField('Signatur', max_length=50)

In the Django Admin Interface, I want to use a filter, which sorts them alphanumeric. Currently, they are sorted this way:

What I'd expect is a list of ["BA 1", "BA 2", ...]. I found admin.SimpleListFilter in the official documentation, which sounds quite suitable. But what I get in the queryset() function is a QuerySet, which can not be sorted in a natural way, because it does not contain the elements, but only the query to the database.

The order_by method on QuerySet gives the same ordering as it can be seen in the image. Is there a way to manipulate the QuerySet to get it naturally sorted?

My code so far:

class AlphanumericSignatureFilter(admin.SimpleListFilter):
    title = 'Signature (alphanumeric)'
    parameter_name = 'signature_alphanumeric'

    def lookups(self, request, model_admin):
        return (
            ('signature', 'Signature (alphanumeric)'),
        )

    def queryset(self, request, queryset: QuerySet):
        return queryset.order_by('signature')

How can I transform the QuerySet to get my desired output? Or is there a different way? The Django Admin Interface is really powerful, which is why I want to use it as long as it is possible. But this feature is really missing.

I am currently using Django 1.11

Any help, comments or hints are appreciated. Thanks for your help.

Answer 1

That's not Django's bug actually, that's how databases work internally and for example looks like MySql for example doesn't have natural sort by default (I googled not a lot, so maybe I am wrong there). But we can use some workaround for the case.

I put everything with examples & screenshots at https://gist.github.com/phpdude/8a45e1bd2943fa806aeffee94877680a

But basically for the given models.py file

from django.db import models


class Item(models.Model):
    signature = models.CharField('Signatur', max_length=50)

    def __str__(self):
        return self.signature

I've used admin.py just for example with the correct filter implementation

from django.contrib.admin import ModelAdmin, register, SimpleListFilter
from django.db.models.functions import Length, StrIndex, Substr, NullIf, Coalesce
from django.db.models import Value as V

from .models import Item


class AlphanumericSignatureFilter(SimpleListFilter):
    title = 'Signature (alphanumeric)'
    parameter_name = 'signature_alphanumeric'

    def lookups(self, request, model_admin):
        return (
            ('signature', 'Signature (alphanumeric)'),
        )

    def queryset(self, request, queryset):
        if self.value() == 'signature':
            return queryset.order_by(
                Coalesce(Substr('signature', V(0), NullIf(StrIndex('signature', V(' ')), V(0))), 'signature'),
                Length('signature'),
                'signature'
            )


@register(Item)
class Item(ModelAdmin):
    list_filter = [AlphanumericSignatureFilter]

Screenshots with examples

A few references:

• http://www.mysqltutorial.org/mysql-natural-sorting/
• https://docs.djangoproject.com/en/2.0/ref/contrib/admin/

PS: It looks like db function Length(column_name) was added on Django 1.9, so you should be able to use it, but generally any Django version supports custom db ORM function call and you can call length() function of the field.

---

Extra example with using Python library natsort

It will work, but requires to load all the possible signatures before for correct sort since it sorts the rows list using python side, not DB side.

It works. But it could be pretty slow in case of a big table size.

From my point of view it should be used only on db tables sizes less than 50 000 rows (for example, depends on your DB server performance & etc).

from django.contrib.admin import ModelAdmin, register, SimpleListFilter
from django.db.models.functions import StrIndex, Concat
from django.db.models import Value as V
from natsort import natsorted

from .models import Item


class AlphanumericTruePythonSignatureFilter(SimpleListFilter):
    title = 'Signature (alphanumeric true python)'
    parameter_name = 'signature_alphanumeric_python'

    def lookups(self, request, model_admin):
        return (
            ('signature', 'Signature (alphanumeric)'),
        )

    def queryset(self, request, queryset):
        if self.value() == 'signature':
            all_ids = list(queryset.values_list('signature', flat=True))
            # let's use "!:!" as a separator for signature values
            all_ids_sorted = "!:!" + "!:!".join(natsorted(all_ids))

            return queryset.order_by(
                StrIndex(V(all_ids_sorted), Concat(V('!:!'), 'signature')),
            )


@register(Item)
class Item(ModelAdmin):
    list_filter = [AlphanumericTruePythonSignatureFilter]

And one more screenshot example for the case