5

So far I have seen only ascending and descending uses for order_by. I want to order_by(Risk) but have the results returned in High, Med, Low order (These are my options in the field), not based on alphabet sorting, the way order_by is done by default.

To explain it a different way...I have a Field in my model called "Risk". Risk only has 3 values across all the records, High,Med,Low. When I run queries on the database I want it to sort the results of the query based on the the field Risk in the order of High,Med,Low, not the random order it is returning the records now.

current code looks like this:

    items = ScanData.objects.filter(Q(Owner__icontains="HooliCorp")).order_by(Risk)
errMSG
  • 65
  • 1
  • 5
  • It is not clear to me *what* you aim to do. Can you share the relevant models and use sample input/output to make clear what you aim to do? – Willem Van Onsem Mar 25 '21 at 16:17
  • 1
    Saving High/Med/Low seems inefficient, save these values as integers which is then easy to sorty by, and map the High/Med/Low to the integers via `choices` https://docs.djangoproject.com/en/3.1/ref/models/fields/#choices – yedpodtrzitko Mar 25 '21 at 16:20
  • Does it answer your question ? https://stackoverflow.com/questions/883575/custom-ordering-in-django – Guillaume Mar 25 '21 at 16:40
  • @yedpodtrzitko - i am dealing with thousands of records and the .csv files that I upload to the database output the risk field with the string High/Med/Low format. I'm trying to avoid changing it to a number based system. – errMSG Mar 25 '21 at 17:26
  • @errMSG if you are dealing with "thousands of records", the issue you should be worried about isnt transforming the data when performing one-time DB INSERT, but the performance you'll be having when doing non-optimal queries on every SELECT. Good luck though. – yedpodtrzitko Apr 06 '21 at 07:52

1 Answers1

2

One solution is to annotate your query with some conditional statement that will output a number depending on the field value:

items = ScanData.objects.filter(
    Q(Owner__icontains="HooliCorp")
).annotate(
    risk_order=models.Case(
        models.When(Risk='Low', then=1),
        models.When(Risk='Med', then=2),
        models.When(Risk='High', then=3),
        output_field=models.IntegerField(),
    ),
).order_by('risk_order')

This will create an annotation on the results list with a number from 1 to 3, depending on the risk value and then it will order the query by this annotation.

GwynBleidD
  • 20,081
  • 5
  • 46
  • 77