使用SQL的CASE WHEN / THEN语法进行排序django [英] ordering using SQL's CASE WHEN/THEN syntax django

问题描述

我正在尝试在表格中订购联系人，我遇到一些问题，我在这个 SO 问题，也许这是一个过度的杀死，但我试图这样做 restapi 所以我只是用这个影响我的休息视图，好吧，这是我的这个订单的解决方案：

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

我这样做是因为我不想改变我的db字段，就像我说的，我只想影响我的休息视图，所以问题是，我做这个过滤器错误所有这一切设置是默认错误的？

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?

模型字段：

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

和此字段的选项：

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"))

推荐答案

是从2012年以来，很多变化，例如包含..... django中的CASE / WHEN 。

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

一个Case（）表达式就像if。 .. elif ... else语句
Python。提供的When（）对象中的每个条件都以
顺序进行计算，直到求值为真值为止。返回匹配的When（）对象的结果表达式
。

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.

标准做法是使用CASE / WHEN创建一个注释，然后在注释中按顺序使用注释

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

这篇关于使用SQL的CASE WHEN / THEN语法进行排序django的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！