Maintain Order of SELECT with IN
So you know that ActiveRecord::Base#find can take an array of ids, right?
User.find(1,2,3)
#=> [#<User …>, #<User …>, #<User …>]
Take a look at this though:
User.find(2,1,3).map(&:id)
#=> [1,2,3]
I’ll bet you didn’t expect that. Okay, maybe you did. But I didn’t expect it.
The above query gets transformed into this SQL:
SELECT * FROM users WHERE id IN (2,1,3)
…which is dandy apart from the fact that (at least in MySQL), IN does not respect ordering. You get the records in whatever way the DB engine feels like giving them to you. This means you generally get something like this:
SELECT id FROM users WHERE id IN (2,1,3)
#=> [1,2,3]
Given I have an already sorted array of ids, I’d prefer not to have to sort the resulting ActiveRecord objects against them (an n^2 operation, I believe). Luckily, MySQL is both the problem and the solution.
Witness, the FIELD operator:
SELECT id FROM users WHERE id IN (2,1,3) ORDER BY FIELD(id,2,1,3) #=> [2,1,3]
Okay, it’s not the prettiest thing in the world, and I’d imagine you don’t want to do it for thousands/millions of records, but for selecting a few things in a certain order it works well.
Here’s what the MySQL manual says about FIELD():
Return the index (position) of the first argument in the subsequent arguments
After squinting at this definition and tilting my head sideways for a while, I think I figured out how this works: The ORDER BY FIELD essentially says “for each row, its position should be the index of its id in the given list”.
So how do you make this work with ActiveRecord? Just add in an order option to your find call:
>> ids = [2,1,3]
>> User.find(ids, :order => "FIELD(id,#{ids.join(',')})"
Again, not the prettiest thing in the world, but it works!
