0

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"))
Community
  • 1
  • 1
copser
  • 2,523
  • 5
  • 38
  • 73

1 Answers1

2

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

e4c5
  • 52,766
  • 11
  • 101
  • 134
  • I see, so creating an annotation will help me order this, and I see this is working in `django=1.8` that is the version I'm using – copser May 04 '17 at 11:04
  • if I remember correctly, that was the version in which this feature was introduced. – e4c5 May 04 '17 at 11:06
  • I'm getting `ValueError` for annotations it said that it's conflicting with my model field, is there a way to over come this – copser May 04 '17 at 11:57
  • There's sure to be but that's a completley different question. Please post a new question with your model and the exact error that you are getting as well as the django query you tried. – e4c5 May 04 '17 at 11:58
  • Ok here it is [link](http://stackoverflow.com/q/43782515/4107823) for my new question, with my custom annotation – copser May 04 '17 at 12:10