In Django, how can I order by a multiple-choice CharField in arbitrary not alphabetical order?

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.

Possible duplicate of [ordering using SQL's CASE WHEN/THEN syntax django](http://stackoverflow.com/questions/43780921/ordering-using-sqls-case-when-then-syntax-django) — e4c5, May 04 '17 at 11:35

Ghopper21 · Accepted Answer · 2013-02-09T00:41:45.967

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.

This concise and easy-to-understand solution worked for me in Django 3.9/PostgreSQL in 2022. While changing the choices to integer fields is also a working solution, it would have caused problems for me because I have other conditions based on the choice names I already had. Changing those names to integers would have made the code harder to read and understand. — David Rhoden, Jan 20 '22 at 11:59

score 3 · Answer 2 · answered Jul 27 '12 at 22:07

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']

Yeah, I don't want to hardcode them in this way. (This shirt example is just an artificial example, by the way.) I'm really hoping there's a simpler way to order by the "map" of a field without having to use a separate table as you suggest, but perhaps there isn't... — Ghopper21, Jul 27 '12 at 22:24
If you don't want to hard-code the available choices then you have to move them into a separate model like I suggest above. That is your only option. — Daniel Eriksson, Jul 28 '12 at 10:11

score 2 · Answer 3 · answered Jul 27 '12 at 21:58

2

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.

answered Jul 27 '12 at 21:58

mVChr

49,587
11
107
104

1

Thanks, but for various reasons, I want the field values themselves to be 'small' etc. (Note this Shirts example is just an artificial example.) – Ghopper21 Jul 27 '12 at 22:25

score 2 · Answer 4 · answered Jul 28 '12 at 09:51

2

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.

answered Jul 28 '12 at 09:51

Josh Smeaton

47,939
24
129
164

1

Thanks, that makes sense, but I want to avoid creating another field or table. (`Shirts` is just a silly example.) See my answer for how I've solved this by in effect creating a custom ordering field on the fly using a SQL CASE WHEN/THEN expression injected into the QuerySet via `extra()`. – Ghopper21 Jul 28 '12 at 13:48

score 1 · Answer 5 · answered Jul 28 '12 at 09:45

1

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

answered Jul 28 '12 at 09:45

Intenex

1,907
3
20
33

Ah... do you mean creating a custom model manager which returns a custom QuerySet subclass with a new order_by_map() (or whatever) that does what I want? If so, that makes sense. It's probably overkill for the silly example I gave above, but if that works it's what I'm looking for. – Ghopper21 Jul 28 '12 at 12:37
Ok, looks like I need to add a QuerySet method for the SQL ORDER BY CASE. Not sure if there is standard SQL syntax for that, of if it's DB-specific though... Am investigating. – Ghopper21 Jul 28 '12 at 12:46
1

Oh wait, I see you are NOT referring to custom Manager/QuerySet -- just sorting the results post-query. Right, that's definitely an option, but not what I'm looking for. For some reason, your answer sparked this thought about customer Manager/QuerySets, so thanks anyhow! – Ghopper21 Jul 28 '12 at 12:47
Lol glad I could help. Good solution btw – Intenex Jul 28 '12 at 23:16

In Django, how can I order by a multiple-choice CharField in arbitrary not alphabetical order?

5 Answers5

Linked