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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With