August 14, 2012

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()

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 = {|id| users.detect {|user| == id } }
# => [100, 1, 6]
Share on Twitter Share the post
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.