ordery_by filter is not working properly in django

March 3, 2022

data = models.Booking.objects.filter(center__isnull=False,org_type='homedx').order_by('-created_at')

return data.order_by( Case( 
                   When ( booking_status="resampling", then=Value(0) ),
                   When ( booking_status="sample not received", then=Value(1)  ),
                   default = Value(1)
                      )
                )

created_at is DateTimeField in Booking Model. Here i have ordered by choice field and for that i have used Case, When and Value method from django.db.models. But when i am using this it is ignoring order_by created_at. I mean when these two conditions are met. i want to get the data in order of latest date. Why it is not working? Thank you !!

>Solution :

Because you now have two .order_by(…) clauses, and as is specified in the documentation of .order_by(…):

Each order_by() call will clear any previous ordering. For example, this query will be ordered by pub_date and not headline:
Entry.objects.order_by('headline').order_by('pub_date')

You can add two fields to the .order_by(…) clause, so:

return models.Booking.objects.filter(center__isnull=False,org_type='homedx').order_by(
    '-created_at',        # 🖘 first field
    Case(                 # 🖘 second field
       When(booking_status='resampling', then=Value(0)),
       default=Value(1)
    ).asc()
)

This will thus first sort on the created_at field, and in case of a tie order by the booking_status. You can swap the two if you want the booking status to take precedence.