Django ORM, Q statements and customized sorting

user_282918 0
Viewed 22

I am simplifying this for clarity. Let's say I had this function that finds Document records based on a requestedColor.

def find_docs(requestedColor):
    docs = Document.objects.filter(Q(color=requestedColor) | Q(other_color=requestedColor))

I'd like to order the results so that Document found using color will appear before objects found with other_color and I want to do it within the query - without any external sorting.

Is there a way to do this within the ORM query? I could not find a way to do that.

Pointers will be appreciated.

django-models django
1 Answers

You can use Conditional Expressions to annotate a value which would indicate which field matched and order by this value:

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

docs = Document.objects.annotate(
    color_order=Case(
        When(color=requestedColor, then=Value(1, output_field=IntegerField())),
        When(other_color=requestedColor, then=Value(2, output_field=IntegerField())),
        default=Value(0, output_field=IntegerField()),
    )
).filter(
    Q(color=requestedColor) | Q(other_color=requestedColor)
).order_by('color_order')
user_149918640
Related