Finding an array of ids while keeping the order with Rails

The order may different

Suppose you're retrieving records using array of ids.

ids = [100, 1, 6]
User.where(:id => ids).map(&:id)
# => [1, 6, 100]

The order of the data may different from ids array, what if you want to keep its order?

for MySQL

There is a function in mysql called FIELD()

FIELD(str,str1,str2,str3,...)
Returns the index (position) of str in the str1, str2, str3, ... list. Returns 0 if str is not found.

So by combing this FIELD() function, here is the code.

ids = [100, 1, 6]
User.where(:id => ids).order("field(id, #{ids.join(',')})").map(&:id)
# => [100, 1, 6]

This will generate SQL below:

SELECT `users`.* FROM `users` WHERE `users`.`id` IN (100, 1, 6) ORDER BY field(id, 100, 1, 6)

for other databases

I didn't dig too much but most of google results suggest sort it manually using ruby.
Here is an example:

ids = [100, 1, 6]
users = User.where(:id => ids)
users = ids.map {|id| users.detect {|user| user.id == id } }
users.map(&:id)
# => [100, 1, 6]
August 14, 2012 Rails

Share on Twitter Share
Qihuan Piao

Qihuan Piao

(aka kinopyo) is Chinese based in Tokyo. Software writer. He shares stories inspired him in this blog. His infamous line - "I feel calm when I kill those monsters, or people (in game)" shocks his friends deeply.
He also writes in Japanese and Chinese.