Raw SQL in order by in django model query

user_4653436 0
Viewed 214

I've a simple query like this (I want 1 BHK to come first, then 2BHK, then anything else)

select *
from service_options
order by case space when '1BHK' then 0 when '2BHK' then 1 else 2 end,
         space

In Django, how to do it? I've a model named ServiceOption

I tried this but no luck.

ServiceOption.objects.order_by(RawSQL("case space when '1BHK' then 0 when '2BHK' then 1 else 2 end,space"), ()).all()

I don't want to execute raw query with something like

ServiceOption.objects.raw("raw query here")

In Laravel, something like this could easily be pulled off like this

Model::query()->orderByRaw('raw order by query here')->get();

Any input will be appreciated. Thank you in advance.

django-rest-framework django-templates django-views django-models django
1 Answers

You can work with a .annotate(…) [Django-doc] and then .order_by(…) [Django-doc]:

from django.db.models import Case, IntegerField, Value, When

ServiceOption.objects.annotate(
    sp=Case(
        When(space='1BHK', then=Value(0)),
        When(space='2BHK', then=Value(1)),
        default=Value(2),
        output_field=IntegerField()
    )
).order_by('sp', 'space')

The raw query would come to this

SELECT *, CASE WHEN "service_options"."space" = 1BHK THEN 0 WHEN "service_options"."space" = 2BHK THEN 1 ELSE 2 END AS "sp" FROM "service_options" ORDER BY "sp" ASC, "service_options"."space" ASC

Since django-3.2 you can work with .alias(…) [Django-doc] to prevent calculating this both as column and in the ORDER BY clause:

from django.db.models import Case, IntegerField, Value, When

ServiceOption.objects.alias(
    sp=Case(
        When(space='1BHK', then=Value(0)),
        When(space='2BHK', then=Value(1)),
        default=Value(2),
        output_field=IntegerField()
    )
).order_by('sp', 'space')

The raw query would come to this

SELECT * FROM "service_options" ORDER BY CASE WHEN ("service_options"."space" = 1BHK) THEN 0 WHEN ("service_options"."space" = 2BHK) THEN 1 ELSE 2 END ASC, "service_options"."space" ASC
user_675790
Related