Sort a Django queryset based on the values in the choice field

Question

I have a model,

class Example(models.Model):
MY_CHOICES = (
    ("yes", _("Yes")),
    ("no", _("NO")),
    ("not_sure", _("Not sure")),
)
name = models.CharField(max_length=200, verbose_name=_('Name'))
status = models.CharField(max_length=100,choices=MY_CHOICES,default='yes')

I need to get the query set sorted in the get_queryset method, ie,

def get_queryset(self, request):
    qs = self.model._default_manager.get_queryset()
    order = ['yes', 'no', 'not_sure']
    qs = #CODE TO ORDER THE S HERE BASED ON order.
    return qs

*The return value i need is a QuerySet not a sorted list. *The qs need to be sorted based on the the status value according to the order 'yes', 'no', 'not_sure'.

Please Note: I need thr QS based on the object attribute value(ie, status value). In the order objects of status='yes' first followed by 'no' and 'not_sure'

make order a choice field and then sort according to the choice where first value will be 1,2,3 and so on like Order_choice=((1, yes), (2, no), (3, not_sure)) — Ajay Gupta, Jul 20 '15 at 13:17
Thanx, But here I need the db value the same 'yes', 'no' and 'not_sure'. This will store the numbers in db. :( — Vipul Vishnu av, Jul 20 '15 at 13:27
wouldn't the meta ordering be enough? `class Meta: ordering = ['status']`. Is there a specific reason for doing it in `get_queryset`? — Pynchia, Jul 20 '15 at 13:27
If the `Meta` ordering does not work, you can always have 3 separate queries - one for `yes`, one for `no` and one for `not_sure`, and return the chained response in the `get_queryset`(The net effect is the same, as the queries are evaluated lazily) — karthikr, Jul 20 '15 at 13:30
@karthikr it's hard to tell what you mean but I don't think that will work, post some code — Anentropic, Jul 20 '15 at 13:33
@Pynchila: Simply giving ordering = ['status'] will not sort according to the values stored based on the choices. May be that will do a alphabetic ordering . Here in the quesryset I need in the order objects with status value 'yes' first followed by 'no', 'not_sure'. — Vipul Vishnu av, Jul 20 '15 at 13:56
OK thank you, it's clear now. I edited the question to explain it better — Pynchia, Jul 20 '15 at 14:06

score 16 · Answer 1 · answered Sep 26 '20 at 00:02

Update for newer Django versions (tested in v3.0.10), without custom SQL syntax, using conditional expressions:

from django.contrib import admin
from django.db.models import Case, When, Value

class ExampleAdmin(admin.ModelAdmin):    
    def get_queryset(self, request):
        qs = super().get_queryset(request)
        return qs.order_by( Case( 
                       When ( status="yes", then=Value(0) ),
                       When ( status="no", then=Value(1)  ),
                       default = Value(2)
                          )
                    )

score 9 · Accepted Answer · edited May 23 '17 at 11:48

Given this previous SO Q/A

and keeping your code, I'd say

def get_queryset(self, request):
    qs = self.model._default_manager.get_queryset()
    order = ['yes', 'no', 'not_sure']
    return sorted(qs, key=lambda x: order.index(x.status))

However, I'd rather have the DB do it instead. Have a look at this QA for a nice trick:

ORDER BY idx(array['yes', 'no', 'not_sure'], status)

Add the SQL fragment above to the query generated by django's ORM (or create one ex-novo) and perform a raw query with it:

def get_queryset(self, request):
    qs = self.model._default_manager.get_queryset()
    newquery = qs.query+' ORDER BY idx(array'+order.__str__()+', status)'
    return self.model._default_manager.raw(newquery)

It should work, provided there is no order by clause already in the sql. I haven't tested it yet.

It's worth noting that this solution uses PostgreSQL-specific syntax. For other DBMSes like MySQL or SQLite, you would need a different syntax (e.g., `ORDER BY FIELD(status, 'yes', 'no', 'not_sure')` for MySQL). [See this SO answer for MySQL details](https://stackoverflow.com/questions/9378613/how-to-define-a-custom-order-by-order-in-mysql) or [this SO answer](https://stackoverflow.com/questions/3303851/sqlite-and-custom-order-by) for SQLite. — kdechant, Sep 03 '20 at 22:04

Sort a Django queryset based on the values in the choice field

2 Answers2