In Django, how can I order by a multiple-choice Ch

Question

Imagine a model Shirts with a size CharField, with values limited to a small number of choices, e.g. 'small', 'medium', 'large', 'xlarge' etc.

To get the shirts grouped by size, you'd do:

Shirts.objects.order_by('size')

But Django will (naturally) order the groups alphabetically, i.e. 'large' then 'medium' then 'small' then 'xlarge'. What I want is to have 'small' before 'medium' before 'large' etc.

I.e. what I naturally want to do is something like the following pseudocode:

size_order = {'small': 1, 'medium': 2, 'large': 3, 'xlarge': 4}
Shirts.objects.order_by('size_order[size]')

What's the best way to accomplish this?

EDIT: See my comments to answers below for thoughts on various suggested approaches. I've stumbled on a custom Manager/QuerySet approach using the SQL ORDER BY CASE syntax which I'm investigating.

Answer 1

I figured out the closest thing to what I'm looking for, which is to use QuerySet.extra() method to take advantage of SQL's CASE WHEN/THEN syntax, which Django doesn't support directly:

CASE_SQL = '(case when size="small" then 1 when size="medium" then 2 when size="large" then 3 when size="xlarge" then 4 end)' 
Shirt.objects.extra(select={'shirt_order': CASE_SQL}, order_by=['shirt_order'])

This may well seem overkill and/or mucky given my (artificial) example, but it's the trick I was looking for! Thanks to everyone for the other perfectly valid approaches to this problem, which somehow indirectly sparked me to figure out this approach.

P.S. It's tempting to create a custom model Manager/QuerySet combo that provides a more native Django-interface for this sort of custom ordering via SQL's CASE WHEN/THEN syntax, but I'll leave that as a homework assignment for myself for another time!

NOTE: The syntax for the CASE WHEN/THEN is database-specific. The syntax above is for SQLite. For PostgreSQL, omit the parentheses and use escaped single quotes instead of double quotes.

Answer 2

If you don't want to store the field values as integers, then the built in order_by() method won't be able to handle your custom case. You'll have to create a function of your own to sort the data once you've retrieved it.

And to do that, of course the best way would be to map your arbitrary values to integers in respective order and then sort by that arrangement :).

Answer 3

Without writing a custom sorting function, just tack on an order field to the model.

class Shirt(models.Model):
    ...
    order = models.IntegerField(default=0)
    class Meta:
        ordering = ('order',)

Shirt.objects.filter(name='Awesome Shirt')

Or, more appropriately, create a new model called Size.

Answer 4

It sounds like you don't want to hard-code the possible choices (because you used a charfield), but at the same time you say there are a small number of choices.

If you are content to hard-code the choices then you could change to an integerfield instead:

class Shirt(models.Model):
  SIZE_CHOICES = (
    (1, u'small'),
    (2, u'medium'),
    (3, u'large'),
    (4, u'x-large'),
    (5, u'xx-large'),
  )
  size = models.IntegerField(choices = SIZE_CHOICES)

If you don't want to hard-code the size choices then you probably want to move the available sizes out to a separate model and reference it as a foreignkey from your Shirt model. To make it arbitrarily sortable you would need an index of some sort other than the primary key that you can sort on. Maybe something like this:

class Size(models.Model):
  sortorder = models.IntegerField()
  name = models.CharField()
  class Meta:
    ordering = ['sortorder']

Answer 5

You should set up your size field with choice tuples ordered the way you want them. In your models.py you'd have something like:

from django.db import models

SHIRT_SIZE_CHOICES = (
    (u"0", u"small"),
    (u"1", u"medium"),
    (u"2", u"large"),
    (u"3", u"xlarge"))

class Shirt(models.Model):
    ...
    size = models.CharField(max_length=2, choices=SHIRT_SIZE_CHOICES)

Then order_by will sort them as you intend.