Complex ordering in django db query

Question

I have the following entries in my database:

`title`            `status`
Titanic            WIP
Avatar             WIP
Terminator         Complete
Abyss              Default

I want to sort these objects by the status of the object: Default, then WIP, then Complete. The correct ordering would then be:

Abyss / Default
Avatar / WIP
Titanic / WIP
Terminator / Complete

How would I do the following db call?

Title.objects.order_by(status='Default', status='WIP', status='Complete',title)

stackoverflow.com/questions/10329849/…

Paolo Bergantino
– Paolo Bergantino

2012-08-16 23:05:37 +00:00
Commented Aug 16, 2012 at 23:05 — Paolo Bergantino
– Paolo Bergantino, Commented Aug 16, 2012 at 23:05
Possible duplicate of Django order_by specific order

Sławomir Lenart
– Sławomir Lenart

2017-08-08 12:07:29 +00:00
Commented Aug 8, 2017 at 12:07 — Sławomir Lenart
– Sławomir Lenart, Commented Aug 8, 2017 at 12:07

David542 · Accepted Answer · 2012-08-19 18:56:28Z

4

To do this query, you can use django's extra:

titles = Title.objects.all()
ordered_query = titles.extra(select={
                'ordering':"(
                    case when status='Default' then 1 
                         when status='WIP' then 2
                         when status='Complete' then 3
                    end)"
                }).order_by('ordering', 'title')

edited Aug 19, 2012 at 18:56

answered Aug 17, 2012 at 2:53

David542

112k211 gold badges583 silver badges1.1k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

andilabs Over a year ago

In Django 1.8 you don't have to use extra you can use Conditional Expressions see here: stackoverflow.com/a/31611225/953553

Collectives™ on Stack Overflow

Complex ordering in django db query

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related