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?

Where are those id's coming from? If it is the UI (via a user selecting them) then scaling shouldn't be an issue, that is the user is unlikely to spend time selecting 1000's of ids). If it's the database (e.g. from a join table), could you store the order in the join table and issue the find based on that? — pauliephonic
– pauliephonic, Commented Apr 29, 2009 at 11:13

tomafro · Accepted Answer · 2009-04-30 07:41:30Z

23

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}}

answered Apr 30, 2009 at 7:41

tomafro

5,8563 gold badges29 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ryan LeCompte Over a year ago

Another solution that is more performant than using #detect to avoid O(N) performance: records = Model.find(ids).group_by(&:id); sorted_records = ids.map { |id| records[id].first }

Substantial · Accepted Answer · 2013-09-24 11:10:07Z

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]

Substantial · Accepted Answer · 2013-09-24 11:10:23Z

8

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(',')})" )

edited Sep 24, 2013 at 11:10

Substantial

6,6822 gold badges33 silver badges40 bronze badges

answered Apr 29, 2009 at 16:13

marcgg

66.9k53 gold badges183 silver badges238 bronze badges

3 Comments

Andrew Grimm Over a year ago

That was the answer suggested in the link "Find by arrays of id in the same order?", but it doesn't seem to work for SQLite.

mahemoff Over a year ago

This no longer works. For more recent Rails: Object.where(id: ids).order("field(id, #{ids.join ','})")

mahemoff Over a year ago

Improved: Object.where(id: ids).order(ids.present? && "field(id, #{ids.join ','})"). This prevents a SQL error if the IDs array is empty or nil.

mu is too short · Accepted Answer · 2016-05-02 05:59:21Z

6

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.

edited May 2, 2016 at 5:59

answered Mar 22, 2013 at 7:08

mu is too short

436k71 gold badges863 silver badges822 bronze badges

4 Comments

Pete Hamilton Over a year ago

I did it with Object.order('case id ' + ids.each_with_index.map { |id, i| "when #{id} then #{i}" }.join(' ') + ' end')

nruth Over a year ago

or to sql injection escape, put this in the map block: sanitize_sql_array(["when ? then ? ", id, i]) (works within an AR model)

mu is too short Over a year ago

@nruth: There was an unstated assumption that ids contained numbers, assumptions are a bad idea (doubly so when they're not explicit) so I've hopefully cleared that up.

nruth Over a year ago

@muistooshort it's still a good answer, and generally a reasonable assumption, especially in 2013. But with javascript front-ends and uuids in fashion it seemed worth a mention, as Google points here for solving the ordering problem.

Community · Accepted Answer · 2017-05-23 10:30:49Z

4

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.

edited May 23, 2017 at 10:30

CommunityBot

11 silver badge

answered Mar 13, 2015 at 18:30

JacobEvelyn

3,9911 gold badge45 silver badges56 bronze badges

Comments

spickermann · Accepted Answer · 2015-04-22 02:17:38Z

3

Justin Weiss wrote a blog article about this problem just two days ago.

It seems to be a good approach to tell the database about the preferred order and load all records sorted in that order directly from the database. Example from his blog article:

# in config/initializers/find_by_ordered_ids.rb
module FindByOrderedIdsActiveRecordExtension
  extend ActiveSupport::Concern
  module ClassMethods
    def find_ordered(ids)
      order_clause = "CASE id "
      ids.each_with_index do |id, index|
        order_clause << "WHEN #{id} THEN #{index} "
      end
      order_clause << "ELSE #{ids.length} END"
      where(id: ids).order(order_clause)
    end
  end
end

ActiveRecord::Base.include(FindByOrderedIdsActiveRecordExtension)

That allows you to write:

Object.find_ordered([2, 1, 3]) # => [2, 1, 3]

edited Apr 22, 2015 at 2:17

answered Apr 22, 2015 at 0:57

spickermann

108k9 gold badges115 silver badges147 bronze badges

9 Comments

Joshua Pinter Over a year ago

Nice drop-in solution but only applies to the id. Would be nice to be able to dynamically determine the attribute to order by, like uid or something.

spickermann Over a year ago

@JoshPinter: Just add a parameter field_name to the method and use that field_name instead of "CASE id" like this: "CASE #{field_name} "

Joshua Pinter Over a year ago

Yep, thanks for the follow-up. Was thinking more of allowing for find_ordered_by_uid(uids) but it's just building off what you said. Cheers.

Joshua Pinter Over a year ago

Recommend changing the name of the method to find_by_ordered_ids to be more explicit and match the name of the module file.

Joshua Pinter Over a year ago

If using a a different field than id that isn't an Integer then make sure you wrap #{id} with parenthesis, like so `"WHEN '#{uid}' THEN #{index} ". Can't thank you enough @spickermann for this little snippet of code - works great!

|

Kanat Bolazar · Accepted Answer · 2015-10-29 20:40:57Z

2

Here's a performant (hash-lookup, not O(n) array search as in detect!) one-liner, as a method:

def find_ordered(model, ids)
  model.find(ids).map{|o| [o.id, o]}.to_h.values_at(*ids)
end

# We get:
ids = [3, 3, 2, 1, 3]
Model.find(ids).map(:id)          == [1, 2, 3]
find_ordered(Model, ids).map(:id) == ids

answered Oct 29, 2015 at 20:40

Kanat Bolazar

5365 silver badges9 bronze badges

Comments

Jeroen van Dijk · Accepted Answer · 2011-08-24 11:00:35Z

1

Another (probably more efficient) way to do it in Ruby:

ids = [5, 2, 3]
records_by_id = Model.find(ids).inject({}) do |result, record| 
  result[record.id] = record
  result
end
sorted_records = ids.map {|id| records_by_id[id] }

answered Aug 24, 2011 at 11:00

Jeroen van Dijk

1,02910 silver badges16 bronze badges

3 Comments

Schneems Over a year ago

you can use each_with_object to make this code two lines FWIW. also did some benchmarking and it looks like this code is much faster if you're iterating over a large set.

Jeroen van Dijk Over a year ago

Thanks for your comment. I didn't know of the existence of #each_with_object (api.rubyonrails.org/classes/…). Is there a significant difference between the above #inject and #each_with_object approach?

Schneems Over a year ago

Nope, each_with_object is essentially the same as inject except you don't have to return the object your modifying (in your case result) at the end of your block. It simplifies building hashes IMHO.

jasongarber · Accepted Answer · 2012-06-04 21:08:04Z

1

Here's the simplest thing I could come up with:

ids = [200, 107, 247, 189]
results = ModelObject.find(ids).group_by(&:id)
sorted_results = ids.map {|id| results[id].first }

answered Jun 4, 2012 at 21:08

jasongarber

2,2062 gold badges19 silver badges12 bronze badges

Comments

jcnnghm · Accepted Answer · 2009-04-29 16:44:37Z

0

@things = [5,2,3].map{|id| Object.find(id)}

This is probably the easiest way, assuming you don't have too many objects to find, since it requires a trip to the database for each id.

answered Apr 29, 2009 at 16:44

jcnnghm

7,4728 gold badges35 silver badges38 bronze badges

2 Comments

Schneems Over a year ago

you shouldn't do this (N+1 queries)

jcnnghm Over a year ago

It's N queries, not N+1. There are much better ways to do this, but as I said, this is the easiest. For performance, look them all up, then dump them in a hash with the ids as keys.

Collectives™ on Stack Overflow

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

10 Answers 10

1 Comment

Comments

3 Comments

4 Comments

Comments

9 Comments

Comments

3 Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

10 Answers 10

1 Comment

Comments

3 Comments

4 Comments

Comments

9 Comments

Comments

3 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related