August 15, 2012 #rails

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?

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)

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]

Archived discussion

Comments from the old blog, preserved as originally posted.

Thanks for the tutorial. This was helpful.

Mysql2 will raise an error

ActiveRecord::StatementInvalid: Mysql2::Error: Incorrect parameters in the call to native function 'field'

May not be the solution

Allen December 21, 2017 · 05:40 UTC

Sorry to bother you again, the error cased by my code, your method works!
Thanks a lot.

This will fail if ids is an empty array because you will get the resulting sql query

ORDER BY FIELD(ids, )

Piao August 30, 2018 · 02:23 UTC

Yeah, good point. Better to have a presence check there before querying the database with that. This post is focused to give an example of how to ensure the order, so I'll keep the code snippet simple 🙂