21

I have a table 'tickets' with the following columns

  • id - primary key - auto increment
  • title - varchar(256)
  • status - smallint(6) - Can have any value between 1 and 5, handled by Django

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?

rubayeet
  • 8,974
  • 8
  • 44
  • 54

3 Answers3

24
q = Ticket.objects.extra(select={'is_top': "status = 4"})
q = q.extra(order_by = ['-is_top'])
Ofri Raviv
  • 23,474
  • 3
  • 53
  • 54
  • 1
    I upvoted this for its neatness, but later noticed a note in the docs saying that .extra() is scheduled to be deprecated. Can this be accomplished another way, e.g. with Annotate? https://docs.djangoproject.com/en/1.10/ref/models/querysets/#extra – shacker Oct 28 '16 at 19:10
11

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.

Hemant_Negi
  • 1,765
  • 1
  • 17
  • 23
5

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

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

jcobacho
  • 53
  • 2
  • 3