Sophisticated order_by sorting

# models.py
# (see the description on the right!)

MNR_SORTER = '''CASE WHEN ('1'||mnr)::integer
                    BETWEEN 15000000 AND 19999999 THEN mnr::integer
                ELSE ('1'||mnr)::integer
                END'''


class CandidateQuerySet(models.query.QuerySet):

    def order_by(self, *field_names):
        assert self.query.can_filter(), \
                "Cannot reorder a query once a slice has been taken."
        if 'mnr' in field_names or '-mnr' in field_names:
            new_field_names = []
            for field_name in field_names:
                if field_name in ('mnr', '-mnr'):
                    field_name = '%s_specialsorted' % field_name
                new_field_names.append(field_name)
            obj = self.extra(select={'mnr_specialsorted': MNR_SORTER})._clone()
        else:
            new_field_names = field_names
            obj = self._clone()
        obj.query.clear_ordering()
        obj.query.add_ordering(*new_field_names)
        return obj


class CandidateManager(models.Manager):

    def get_query_set(self):
        """Returns a new QuerySet object.  Subclasses can override this method
        to easily customize the behavior of the Manager.
        """
        return CandidateQuerySet(self.model, using=self._db)


class Candidate(models.Model):
    mnr = models.CharField(max_length=256)  # Matrikelnumer
    firstname = models.CharField(max_length=4096)
    lastname = models.CharField(max_length=4096)

    objects = CandidateManager()

######
# To make the search really fast, you need to create a functional index.
# For PostgreSQL this would look like this:
#
# create a file "MYAPP/sql/candidate_postgresql_psycopg2.sql" with this content:

DROP INDEX IF EXISTS candidates_mnr_specialsorted;
CREATE INDEX candidates_mnr_specialsorted ON candidates_candidate ( ( CASE WHEN ('1'||mnr)::integer
                    BETWEEN 15000000 AND 19999999 THEN mnr::integer
                ELSE ('1'||mnr)::integer
                END ) );

# This index will be (re)created upon a syncdb. It is also a good idea to do a "VACUUM ANALYZE" in a psql shell for PostgreSQL to recognise whether to use the index or not.