Custom ordering in Django

Question

How do you define a specific ordering in Django QuerySets?

Specifically, if I have a QuerySet like so: ['a10', 'a1', 'a2'].

Regular order (using Whatever.objects.order_by('someField')) will give me ['a1', 'a10', 'a2'], while I am looking for: ['a1', 'a2', 'a10'].

What is the proper way to define my own ordering technique?

score 50 · Accepted Answer · edited May 28 '14 at 08:36

50

As far as I'm aware, there's no way to specify database-side ordering in this way as it would be too backend-specific. You may wish to resort to good old-fashioned Python sorting:

class Foo(models.Model):
    name = models.CharField(max_length=128)

Foo.objects.create(name='a10')
Foo.objects.create(name='a1')
Foo.objects.create(name='a2')

ordered = sorted(Foo.objects.all(), key=lambda n: (n[0], int(n[1:])))
print ordered # yields a1, a2, 10

If you find yourself needing this kind of sorting a lot, I'd recommend making a custom models.Manager subclass for your model that performs the ordering. Something like:

class FooManager(models.Manager):
    def in_a_number_order(self, *args, **kwargs):
        qs = self.get_query_set().filter(*args, **kwargs)
        return sorted(qs, key=lambda n: (n[0], int(n[1:])))

class Foo(models.Model):
    ... as before ...
    objects = FooManager()

print Foo.objects.in_a_number_order()
print Foo.objects.in_a_number_order(id__in=[5, 4, 3]) # or any filtering expression

edited May 28 '14 at 08:36

dhana

6,487
4
40
63

answered May 19 '09 at 16:03

Jarret Hardie

95,172
10
132
126

2

I get this error when trying this method: : 'Model' object is unindexable – ninja123 Jun 04 '10 at 07:27
Does `sorted` return a queryset? Given that it's passed a queryset, I would think so, but I'm thinking it might also be converted to a list or something internally for sorting, then *that* gets returned. – John C Sep 22 '12 at 20:13
4

I'm afraid `sorted` does not return a queryset; it is a built-in python function, so it doesn't know anything about Django. Django's querysets implement python's interface for iterables, so `sorted` is able to work on them because it thinks it has just been passed a list. If you need to return a QS, AFAIK there's no easy way to create a QS off the list that sorted returns... it IS possible to fake a QS (basically implement its interface), but that can be tricky. There are many Manager methods that don't return a QS, and this sorting function would be consistent with those, I'm afraid. – Jarret Hardie Sep 23 '12 at 07:54

score 39 · Answer 2 · answered May 20 '09 at 18:20

39

@Jarret's answer (do the sort in Python) works great for simple cases. As soon as you have a large table and want to, say, pull only the first page of results sorted in a certain way, this approach breaks (you have to pull every single row from the database before you can do the sort). At that point I would look into adding a denormalized "sort" field which you populate from the "name" field at save-time, that you can sort on at the DB-level in the usual way.

answered May 20 '09 at 18:20

Carl Meyer

122,012
20
106
116

4

+1 for addressing the performance-related issues; in fact, your suggestion is one we've used on some of our data that is used for reports, in a DB-agnostic environment, and we've got tens-of-millions of rows, so I can vouch that it works well. – Jarret Hardie May 20 '09 at 23:39

score 1 · Answer 3 · answered May 19 '09 at 16:04

It depends on where you want to use it.

If you want to use it in your own templates, I would suggest to write a template-tag, that will do the ordering for you In it, you could use any sorting algorithm you want to use.

In admin I do custom sorting by extending the templates to my needs and loading a template-tag as described above

score 0 · Answer 4 · answered Nov 04 '14 at 09:31

If you have larger data sets and additionally use a SOLR backend (e.g. with Haystack):

Use solr.ICUCollationField with the numeric=true option as type for the sort fields. This will sort according to language and if numbers are present will sort the number part according to numeric rules instead of string sorting.

See: https://cwiki.apache.org/confluence/display/solr/Language+Analysis#LanguageAnalysis-UnicodeCollation http://www.solr-start.com/javadoc/solr-lucene/org/apache/solr/schema/ICUCollationField.html

Custom ordering in Django

4 Answers4

Linked