Use ORDER BY FIELD(name, 'Apple', 'Banana') in django

Question

I would like to use ORDER BY FIELD() (MySQL) in Django. Does Django support that?

So in MySQL it looks like:

SELECT * FROM fruit 
ORDER BY FIELD(name, 'Banana', 'Apple', 'Pear', 'Orange'), variety;

I would like use this in django ORM. How can i do that. Can i do that using order_by(). Or using extra() somehow.

Does not work like this:

Fruit.objects.all().extra(order_by="FIELD(name, 'Banana', 'Apple', 'Pear', 'Orange')")

score 9 · Accepted Answer · answered Jun 29 '12 at 19:19

9

Fruit.objects.extra(
    select={'manual': "FIELD(name, 'Banana', 'Apple', 'Pear', 'Orange')"},
    order_by=['manual']
)

answered Jun 29 '12 at 19:19

Chris Pratt

1

I was doing google-fu. Maybe you are black belt owner in google-fu? What technique di you use? – Pol Jun 29 '12 at 19:28
It'll just piss you off, but all I did was search for "django order by field mysql". First result ;) – Chris Pratt Jun 29 '12 at 19:49
1

Is this portable? I mean, works also in PostgreSQL? My case is something similar, but with a "featured" boolean field. I want to always get the featured items first, and then apply more ordering options. – Armando Pérez Marqués Aug 13 '12 at 15:42
No, this will not work with Postgres, but see http://stackoverflow.com/questions/1309624/simulating-mysqls-order-by-field-in-postgresql for some possible workarounds. – Chris Pratt Aug 13 '12 at 15:52

1 Answers1