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