views:

475

answers:

3

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?

+7  A: 

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
Jarret Hardie
Spot on! Wonderful answer, thanks!
Yuval A
I get this error when trying this method: : 'Model' object is unindexable
ninja123
A: 

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

vikingosegundo
+2  A: 

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

Carl Meyer
+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