python case when用法,使用SQL的CASE WHEN / THEN语法进行排序django

I'm trying to order contacts in table and I'm facing some problems, I found a nice solution in this SO question, maybe it's an over kill for this, but I'm trying to do this over restapi so I'm only affecting my rest view with this, ok so this is my solution for this ordering:

filter_date = self.request.query_params.get('filter_date', None)

case_sql = '(case when status="Client" ' \

'then 1 when status="Contacted" ' \

'then 2 when status="Qualified" ' \

'then 3 when status="Virgin" then 4 end)'

if filter_date is not None:

queryset = queryset.filter(next_action_date=filter_date).extra(select={'status': case_sql},

order_by=['status'])

I'm doing this because I don't want to change my db field, like I said I only want to affect my rest view, so the question is, am I doing this filter wrong all this all setup is wrong by default?

Model fields:

status = models.CharField(max_length=10, choices=LeadContactConstants.STATUSES, default=LeadContactConstants.STATUS_PRISTINE)

and the choices for this field:

class LeadContactConstants(object):

STATUS_PRISTINE = "PRISTINE"

STATUS_CONTACTED = "CONTACTED"

STATUS_QUALIFIED = "QUALIFIED"

STATUS_CLIENT = "CLIENT"

STATUSES = ((STATUS_PRISTINE, "Virgin"),

(STATUS_CONTACTED, "Contacted"),

(STATUS_QUALIFIED, "Qualified"),

(STATUS_CLIENT, "Client"))

解决方案

That question you have linked to is from 2012. A lot has changed since then, such as the inclusion of ..... CASE/WHEN in django.

A Case() expression is like the if ... elif ... else statement in

Python. Each condition in the provided When() objects is evaluated in

order, until one evaluates to a truthful value. The result expression

from the matching When() object is returned.

The whole idea being that you don't need to write complicated queries as people sometimes had to in the past.

The standard practice is to create an annotation with CASE/WHEN and then use that in annotation in the order by