Can I load ActiveRecord-Objects in predetermined ordering?

Question

Is it possible to construct a scope like this

User.where(:id => [5,4,3,2,7,1000] )

in a way, that

a) the results are in the order of the ids passed. (This is the simple part, since you can just load the records, and sort the result accordingly)

b) the result is not an array but a scope in itself (The tricky part ;)

Update: Solutions that only work on MySQL are welcome.

which db? mysql has ORDER BY FIELD. do you want a portable solution? — tokland, Dec 13 '11 at 14:24

Mark Thomas · Accepted Answer · 2011-12-13T14:42:25.587

1

I can think of two ways to do it:

(1) Put an order column in the database.

(2) Use raw SQL and a custom ORDER BY clause like so:

order by (
    case id
    when 5 then 0 
    when 4 then 1
    when 3 then 2
    when 2 then 3
    when 7 then 4
    when 1000 then 5
    end
)

I'm not sure how portable that is.

edited Dec 13 '11 at 14:42

answered Dec 13 '11 at 14:35

Mark Thomas

Solution (2) works in MySQL and SQLite3... good enough for me! – flitzwald Dec 13 '11 at 14:48

score 0 · Answer 2 · answered Dec 13 '11 at 14:33

Different databases hold their data in different way, so that noone can surely answer which way it will be ranged.

But if you really challenging this problem why don't use raw SQL, select each tuple and merge it with union while constructing a long SQL-query.

2 Answers2