CODEWITHSUNDEEP
pythonsqldjangopostgresql
Bartosz Karpiński
Bartosz Karpiński

Reputation: 487

Django convert raw SQL ordering to QuerySet API

I'm trying to perform natural ordering on CharField for model (here simplified version):

class House(models.Model):
    number = models.CharField(max_length=32)

def __str__(self):
    return str(self.number)

>>> House.objects.all()
<QuerySet [<House: 2>, <House: 1>, <House: 4>, <House: 3>, <House: 1b>, <House: 2b>, <House: 1c>, <House: 13c>, <House: 10>, <House: 2e>, <House: 3ab>]>

I have managed to order it by raw SQL query:

House.objects.raw("SELECT * FROM entry_house ORDER BY(substring(number, '^[0-9]+'))::int, substring(number, '[0-9].*$')")

But I need to get QuerySet object not RawQuerySet (for our current pagination implementation). Iterating over all raw queryset is not an option because of huge amount of data.

How can I convert result of RawQuerySet to QuerySet or even better, how to convert this raw SQL query to Django QuerySet API?

Upvotes: 0

Views: 756

Answers (1)

user2390182
user2390182

Reputation: 73450

With your postgres backend, you can do something like the following, using the regexp_replace function:

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

House.objects.all()\
    .annotate(inumber=Cast(
         Func(F('number'),    # take number
              Value('[^\d]'), # pattern to replace: non-digits
              Value(''),      # replacement
              Value('g'),     # global flag; replace all occurrences
              function='regexp_replace'), 
         IntegerField()))\    # output type of cast
    .order_by('inumber', 'number')  # fallback to non-digit portion

The Func removes non-digits from number (Django database functions). The annotation casts the result to an integer. There might be even better functions available, but for the above data, this should work.

Upvotes: 2

Related Questions