Sort ordering in Django: possible to ignore "The" prefix when sorting?

Question

This seems like the kind of thing Django makes simple, wondering if anyone has any experience with it.

I have a table of bands, some of whom are called 'The Geeks' (for example). I want them to appear alphabetically under 'G'.

I know I can do some fancy SQL to rewrite them on the fly and sort that way, but does Django offer anything built-in so I can keep my nice Band.objects.all() syntax and still filter on a custom sort?

I know I could change my data to include a has_prefix field or something and store the bandname as 'Geeks, The' or whatever, but I'd rather not touch the data itself if possible.

Any tips?

score 3 · Answer 1 · answered Aug 21 '12 at 20:34

Here's how I did it on a recent Django project:

from django.db import models

SomeClass(models.Model):
    title = models.CharField()

    @property
    def alphabetical_title(self):
        """
        Returns an alphabetical-friendly string of a title attribute.
        """
        title = self.title

        # A list of flags to check each `title` against.
        starts_with_flags = [
            'the ',
            'an ',
            'a '
        ]

        # Check each flag to see if the title starts with one of it's contents.
        for flag in starts_with_flags:
            if title.lower().startswith(flag):
                # If the title does indeed start with a flag, return the title with
                # the flag appended to the end preceded by a comma.
                return "%s, %s" % (title[len(flag):], title[:len(flag)-1])
            else:
                pass
        # If the property did not return as a result of the previous for loop then just
        # return the title.
        return self.title

Since this is a property and not an actual column in the database I need to retrieve a QuerySet first and then sort it after the fact in a view. Here's how I did that:

from operator import attrgetter
from someapp.models import SomeClass

some_objects = SomeClass.objects.all()
sorted_objects = sorted(some_objects, key=attrgetter('alphabetical_title'), reverse=False)

I'm sure you've long since found a solution but I figured it might help to post it anyways as someone else in the future might run into this same problem.

score 2 · Accepted Answer · edited May 23 '17 at 12:02

2

You can't do it with order_by()

You can

Do some fancy SQL. If you want to keep it nice and short, you should write a custom model manager.
Do sorting at Python-level, not SQL-level
Store "sort title" and "display title" separately.

edited May 23 '17 at 12:02

Community

1
1

answered May 19 '11 at 11:45

DrTyrsa

31,014
7
86
86

One thing just occurred to me - could I put a function on the model object that returns the 'Band, The' style name, and sort on that? – Matt Andrews May 19 '11 at 11:58
1

`order_by` is executed at DB level, your DB knows nothing about python functions. But of course you can sort the list at python-level (#2 in my answer). – DrTyrsa May 19 '11 at 12:01
For option 3 above, it would probably be better to store a properly ordered sequence number in the "sort title" field, rather than repeating the whole title again with a variation in word order. Lots less space used. – Ubuntourist Sep 06 '12 at 21:22
@Ubuntourist That would really cost you on insert, though. – Mike Pelley May 30 '13 at 16:59
@MikePelley Although far from a perfect solution, I wouldn't worry about it on insert, leaving the numeric sort field in an incorrect state, and then periodically would do a "batch" resorting. – Ubuntourist May 31 '13 at 18:22
@Ubuntourist You mean you would leave it incorrectly sorted for the users of the site, such that new entries appear at the top or the bottom of the list? – Mike Pelley Jun 02 '13 at 21:20
@MikePelley, temporarily, yes. Hence the comment about it being far from perfect. How often one did a scheduled resort would depend on site usage, vs. resources the powers-that-be want to devote to resorting. – Ubuntourist Jun 03 '13 at 19:54

score 1 · Answer 3 · answered Dec 16 '17 at 00:55

Using the model and a couple hints from the above (by respondcreate), and an adaptation of this answer, I came up with the following one line approach:

mysortedlist = sorted([x for x in SomeClass.all()],
               key=lambda y: re.sub("^(the|a|an) ","",y.title.lower()) )

This generates a list from the QuerySet and sorts it according to a lambda function that replaces the, a, and an at the beginning of a lowercased version of the title.

Sort ordering in Django: possible to ignore "The" prefix when sorting?

3 Answers3