Find model records by ID in the order the array of IDs were given

Question

I have a query to get the IDs of people in a particular order, say: ids = [1, 3, 5, 9, 6, 2]

I then want to fetch those people by Person.find(ids)

But they are always fetched in numerical order, I know this by performing:

people = Person.find(ids).map(&:id)  => [1, 2, 3, 5, 6, 9] 

How can I run this query so that the order is the same as the order of the ids array?

I made this task more difficult as I wanted to only perform the query to fetch people once, from the IDs given. So, performing multiple queries is out of the question.

I tried something like:

ids.each do |i|   person = people.where('id = ?', i) 

But I don't think this works.

Answer 1

Note on this code:

ids.each do |i|   person = people.where('id = ?', i) 

There are two issues with it:

First, the #each method returns the array it iterated on, so you'd just get the ids back. What you want is a collect

Second, the where will return an Arel::Relation object, which in the end will evaluate as an array. So you'd end up with an array of arrays. You could fix two ways.

The first way would be by flattening:

ids.collect {|i| Person.where('id => ?', i) }.flatten 

Even better version:

ids.collect {|i| Person.where(:id => i) }.flatten 

A second way would by to simply do a find:

ids.collect {|i| Person.find(i) } 

That's nice and simple

You'll find, however, that these all do a query for each iteration, so not very efficient.

I like Sergio's solution, but here's another I would have suggested:

people_by_id = Person.find(ids).index_by(&:id) # Gives you a hash indexed by ID ids.collect {|id| people_by_id[id] } 

I swear that I remember that ActiveRecord used to do this ID ordering for us. Maybe it went away with Arel ;)

Answer 2

As I see it, you can either map the IDs or sort the result. For the latter, there already are solutions, though I find them inefficient.

Mapping the IDs:

ids = [1, 3, 5, 9, 6, 2] people_in_order = ids.map { |id| Person.find(id) } 

Note that this will cause multiple queries to be executed, which is potentially inefficient.

Sorting the result:

ids = [1, 3, 5, 9, 6, 2] id_indices = Hash[ids.map.with_index { |id,idx| [id,idx] }] # requires ruby 1.8.7+ people_in_order = Person.find(ids).sort_by { |person| id_indices[person.id] } 

Or, expanding on Brian Underwoods answer:

ids = [1, 3, 5, 9, 6, 2] indexed_people = Person.find(ids).index_by(&:id) # I didn't know this method, TIL :) people_in_order = indexed_people.values_at(*ids) 

Hope that helps