CODEWITHSUNDEEP
pythonmysqldjangoorm
rubayeet
rubayeet

Reputation: 9400

Can Django ORM do an ORDER BY on a specific value of a column?

I have a table 'tickets' with the following columns

When I'll do a SELECT * I want the rows with status = 4 at the top, the other records will follow them. It can be achieved by the following query:

select * from tickets order by status=4 DESC

Can this query be executed through Django ORM? What parameters should be passed to the QuerySet.order_by() method?

Upvotes: 26

Views: 10845

Answers (4)

Jack X
Jack X

Reputation: 21

You can use simple a .annotate() to add a True or False value for any given condition. In this case status = 4 is the first order_by parameter, but it could be combined with any additional conditions.

from django.db.models import Q

tickets = Tickets.objects.all() ## Or .filter(**Your ciriteria**)
tickets = tickets.annotate(
     priority1=Q(status=4),
     priority2=Q(**Some Other Condition**),
     [...]
     )

tickets = tickets.order_by("-priority1", "-priority2", [...])

Upvotes: 2

jcobacho
jcobacho

Reputation: 378

For those in need just like me that stumbled on this now and are using newer versions of Django

from django.db.models import Case, When

Ticket.objects.annotate(
    relevancy=Case(
        When(status=4, then=1),
        When(status=3, then=2),
        When(status=2, then=3), 
        output_field=IntegerField()
    )
).order_by('-relevancy')

Using Count() will return 1 or 0 depending if your case was found or not. Not ideal if ordering by a couple of status

Upvotes: 10

Hemant_Negi
Hemant_Negi

Reputation: 2078

I did this while using PostgresSql with django.

from django.db.models import Case, Count, When

Ticket.objects.annotate(
    relevancy=Count(Case(When(status=4, then=1)))
).order_by('-relevancy')

It will return all objects from Ticket, but tickets with status = 4 will be at the beginning.

Hope someone will find it useful.

Upvotes: 16

Ofri Raviv
Ofri Raviv

Reputation: 24823

q = Ticket.objects.extra(select={'is_top': "status = 4"})
q = q.extra(order_by = ['-is_top'])

Upvotes: 25

Related Questions