Activerecord ORDER BY FIELD

Question

I am trying to do a "ORDER BY FIELD" in active record. I know that i can do it with MySQL but i am trying to keep my app agnostic and follow best practice. my production db is PostgreSQL

the MySQL looks like

SELECT id, name
FROM mytable
WHERE name IN ('B', 'A', 'D', 'E', 'C')
ORDER BY FIELD(name, 'B', 'A', 'D', 'E', 'C')

Asked and answered I think : http://stackoverflow.com/questions/1309624/simulating-mysqls-order-by-field-in-postgresql — xQbert, Nov 11 '13 at 19:30
Thanks that helps but i don't want to Simulating MySQL's in PG but i want to be able to do it with active record. Thanks for the help — MZaragoza, Nov 11 '13 at 19:33

score 2 · Accepted Answer · answered Nov 11 '13 at 19:31

2

I was trying to make it happen and this is what i came with. I still think that this can be a bit cleaner

Model.select(:id).select(:name).order("status = 'B' DESC, status = 'A' DESC, status = 'D' DESC, status = 'E' DESC, status = 'C' DESC")

answered Nov 11 '13 at 19:31

MZaragoza

10,108
9
71
116

Use a CASE statement like in the duplicate. – mu is too short Nov 11 '13 at 19:58
@muistooshort how would you add a case statement on the order? – MZaragoza Nov 11 '13 at 21:20
Exactly the way [the dup](http://stackoverflow.com/questions/1309624/simulating-mysqls-order-by-field-in-postgresql) does: `order("case status when 'B' ...")`. AR doesn't solve all your database portability needs, not even close. – mu is too short Nov 11 '13 at 21:52
I just released this gem which bundles this functionality: https://github.com/panorama-ed/order_as_specified – JacobEvelyn Mar 13 '15 at 18:19

score 1 · Answer 2 · answered Nov 11 '13 at 22:16

1

One agnostic option is to move your status values into another model and have an extra attribute for the sort order.

answered Nov 11 '13 at 22:16

David Aldridge

51,479
8
68
96

Thanks i like that solution. and if i could not get this to work that is what i am going to do – MZaragoza Nov 11 '13 at 22:19

Activerecord ORDER BY FIELD

2 Answers2