Clean way to find ActiveRecord objects by id in the order specified

Question

I want to obtain an array of ActiveRecord objects given an array of ids.

I assumed that

Object.find([5,2,3])

Would return an array with object 5, object 2, then object 3 in that order, but instead I get an array ordered as object 2, object 3 and then object 5.

The ActiveRecord Base find method API mentions that you shouldn't expect it in the order provided (other documentation doesn't give this warning).

One potential solution was given in Find by array of ids in the same order?, but the order option doesn't seem to be valid for SQLite.

I can write some ruby code to sort the objects myself (either somewhat simple and poorly scaling or better scaling and more complex), but is there A Better Way?

Solution 1

It's not that MySQL and other DBs sort things on their own, it's that they don't sort them. When you call Model.find([5, 2, 3]), the SQL generated is something like:

SELECT * FROM models WHERE models.id IN (5, 2, 3)

This doesn't specify an order, just the set of records you want returned. It turns out that generally MySQL will return the database rows in 'id' order, but there's no guarantee of this.

The only way to get the database to return records in a guaranteed order is to add an order clause. If your records will always be returned in a particular order, then you can add a sort column to the db and do Model.find([5, 2, 3], :order => 'sort_column'). If this isn't the case, you'll have to do the sorting in code:

ids = [5, 2, 3]
records = Model.find(ids)
sorted_records = ids.collect {|id| records.detect {|x| x.id == id}} 

Solution 2

Based on my previous comment to Jeroen van Dijk you can do this more efficiently and in two lines using each_with_object

result_hash = Model.find(ids).each_with_object({}) {|result,result_hash| result_hash[result.id] = result }
ids.map {|id| result_hash[id]}

For reference here is the benchmark i used

ids = [5,3,1,4,11,13,10]
results = Model.find(ids)

Benchmark.measure do 
  100000.times do 
    result_hash = results.each_with_object({}) {|result,result_hash| result_hash[result.id] = result }
    ids.map {|id| result_hash[id]}
  end
end.real
#=>  4.45757484436035 seconds

Now the other one

ids = [5,3,1,4,11,13,10]
results = Model.find(ids)
Benchmark.measure do 
  100000.times do 
    ids.collect {|id| results.detect {|result| result.id == id}}
  end
end.real
# => 6.10875988006592

Update

You can do this in most using order and case statements, here is a class method you could use.

def self.order_by_ids(ids)
  order_by = ["case"]
  ids.each_with_index.map do |id, index|
    order_by << "WHEN id='#{id}' THEN #{index}"
  end
  order_by << "end"
  order(order_by.join(" "))
end

#   User.where(:id => [3,2,1]).order_by_ids([3,2,1]).map(&:id) 
#   #=> [3,2,1]

Solution 3

Apparently mySQL and other DB management system sort things on their own. I think that you can bypass that doing :

ids = [5,2,3]
@things = Object.find( ids, :order => "field(id,#{ids.join(',')})" )

Solution 4

A portable solution would be to use an SQL CASE statement in your ORDER BY. You can use pretty much any expression in an ORDER BY and a CASE can be used as an inlined lookup table. For example, the SQL you're after would look like this:

select ...
order by
    case id
    when 5 then 0
    when 2 then 1
    when 3 then 2
    end

That's pretty easy to generate with a bit of Ruby:

ids = [5, 2, 3]
order = 'case id ' + (0 .. ids.length).map { |i| "when #{ids[i]} then #{i}" }.join(' ') + ' end'

The above assumes that you're working with numbers or some other safe values in ids; if that's not the case then you'd want to use connection.quote or one of the ActiveRecord SQL sanitizer methods to properly quote your ids.

Then use the order string as your ordering condition:

Object.find(ids, :order => order)

or in the modern world:

Object.where(:id => ids).order(order)

This is a bit verbose but it should work the same with any SQL database and it isn't that difficult to hide the ugliness.

Solution 5

As I answered here, I just released a gem (order_as_specified) that allows you to do native SQL ordering like this:

Object.where(id: [5, 2, 3]).order_as_specified(id: [5, 2, 3])

Just tested and it works in SQLite.

View more solutions