CODEWITHSUNDEEP
pythondjangodjango-models
Yuval Adam
Yuval Adam

Reputation: 165212

Custom ordering in Django

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?

Upvotes: 43

Views: 37752

Answers (5)

proselotis
proselotis

Reputation: 11

A work around for solving this that works with a small number of unique keys to sort on is creating a python dictionary mapping values to a number (that represents a sorting order you want). Then using annotate to add a new variable to your query set and finally sorting on that variable.

from django.db.models import Case, When, IntegerField
# Define your custom sorting priority
sort_priority = {
        "A01" : 1,
        "A02" : 2,
        "A10" : 3,
        "B10" : 4,
        "B02" : 5
    }
# Create new variable called position_value that are the values in your sorting dictionary 
queryset = queryset.annotate(
        position_value=Case(
            *[When(your_variable=key, then=value) for key, value in sort_priority.items()],
            output_field=IntegerField(),
        )
    )

# Sort the queryset by the mapped values
sorted_queryset = queryset.order_by('position_value')

Upvotes: 0

Risadinha
Risadinha

Reputation: 16666

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

Upvotes: 0

Jarret Hardie
Jarret Hardie

Reputation: 97902

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

Upvotes: 52

Carl Meyer
Carl Meyer

Reputation: 126541

@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.

Upvotes: 41

vikingosegundo
vikingosegundo

Reputation: 52227

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

Upvotes: 1

Related Questions