How to natural sort a QuerySet with Postgres and SQLite

I have the following model:

from django.db import models

class VersionInfo(models.Model):
    version = models.CharField("Version", max_length=16)  # 3-digit version like "1.2.3".

I have to natural sort the QuerySet by the 3 parts of "version" when rendering a django_tables2 table. I already tried things like this, but it doesn't sort like expected:

from django.db.models import Func, IntegerField, Value
from django.db.models.functions import Cast

class SplitPart(Func):
    function = 'SUBSTR'
    template = "%(function)s(%(expressions)s)"

class VersionInfoListView(LoginRequiredMixin, PermissionRequiredMixin, SingleTableMixin, FilterView):

    def get_queryset(self):
        qs = super().get_queryset()
        # noinspection PyUnresolvedReferences
        if not self.request.user.is_sys_admin():
            qs = qs.filter(is_approved=True)

        qs = qs.annotate(
            major=Cast(SplitPart('version', 1, Func('version', Value('.'), function='INSTR') - 1), IntegerField()),
            minor=Cast(
                SplitPart(
                    'version',
                    Func('version', Value('.'), function='INSTR') + 1,
                    Func(SplitPart('version', Func('version', Value('.'), function='INSTR') + 1), Value('.'), function='INSTR') - 1,
                ),
                IntegerField(),
            ),
            patch=Cast(
                SplitPart(
                    'version',
                    Func(SplitPart('version', Func('version', Value('.'), function='INSTR') + 1), Value('.'), function='INSTR')
                    + Func('version', Value('.'), function='INSTR')
                    + 1,
                ),
                IntegerField(),
            ),
        ).order_by('major', 'minor', 'patch')

Yes, it's very ugly, but should work in SQLite and Postgres (SQLite has no STRING_TO_ARRAY function), but it doesn't. Does anybody have an idea? Is there a library providing this functionality?

Вернуться на верх