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

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

In older version of rails, find and where fetch data in numerical order, but rails 5 fetch data in the same order in which you query it

Note: find preserve the order and where don't preserve it

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

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

Answer 2

Editor's note:
As of Rails 5, find returns the records in the same order as the provided IDs (docs).

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 3

Use find:

Thing.find([4, 2, 6])

For Rails 7:

Thing.where(id: [4, 2, 6]).in_order_of(:id, [4, 2, 6])

See https://hashrocket.com/blog/posts/return-results-using-a-specific-order-in-rails

Answer 4

I here summarise the solutions, plus adding recent (9.4+) PostgreSQL-specific solution. The following is based on Rails 6.1 and PostgreSQL 12. Though I mention solutions for earlier versions of Rails and PostgreSQL, I haven't actually tested them with earlier versions.

For reference, this question "ORDER BY the IN value list" gives various ways of sorting/ordering with the database.

Here, I assume the model is guaranteed to have all the records specified by the Array of IDs, ids. Otherwise, an exception like ActiveRecord::RecordNotFound may be raised (or may not, depending on the way).

What does NOT work

Person.where(id: ids)

The order of the returned Relation is either arbitrary or that of the numerical values of the primary IDs; whichever, it usually does not agree with that of ids.

Simple solution to get an Array

(Rails 5+ only(?))

Person.find ids

which returns a Ruby Array of Person models in the order of the given ids.

A downside is you cannot further modify the result with SQL.

In Rails 3, the following is the way apparently, though this may not work (certainly does not in Rails 6) in the other versions of Rails.

Person.find_all_by_id ids

Pure Ruby solution to get an Array

Two ways. Either works regardless of Rails versions (I think).

Person.where(id: ids).sort_by{|i| ids.index(i.id)}
Person.where(id: ids).index_by(&:id).values_at(*ids)

which returns a Ruby Array of Person models in the order of the given ids.

DB-level solution to get a Relation

All of the following return Person::ActiveRecord_Relation, to which you can apply more filters if you like.

In the following solutions, all records are preserved, including those whose IDs are not included in the given array ids. You can filter them out any time by adding where(id: ids) (this sort of flexibility is a beauty of ActiveRecord_Relation).

For any Database

Based on user3033467's answer but updated to work with Rails 6 (which has disabled some features with order() due to a security concern; see "Updates for SQL Injection in Rails 6.1" by Justin for the background).

order_query = <<-SQL
  CASE musics.id 
    #{ids.map.with_index { |id, index| "WHEN #{id} THEN #{index}" } .join(' ')}
    ELSE #{ids.length}
  END
SQL

Person.order(Arel.sql(order_query))

For MySQL specific

From Koen's answer (I haven't tested it).

Person.order(Person.send(:sanitize_sql_array, ['FIELD(id, ?)', ids])).find(ids)

For PostgreSQL specific

PostgreSQL 9.4+

join_sql = "INNER JOIN unnest('{#{ids.join(',')}}'::int[]) WITH ORDINALITY t(id, ord) USING (id)"
Person.joins(join_sql).order("t.ord")

PostgreSQL 8.2+

Based on Jerph's answer, but LEFT JOIN is replaced with INNER JOIN:

val_ids = ids.map.with_index.map{|id, i| "(#{id}, #{i})"}.join(", ")
Person.joins("INNER JOIN (VALUES #{val_ids}) AS persons_id_order(id, ordering) ON persons.id = persons_id_order.id")
  .order("persons_id_order.ordering")

To get lower-level objects

The following is solutions to get lower-level objects. In a vast majority of cases, the solutions described above must be superior to these, but am putting there here for the sake of completeness (and record before I found better solutions)…

In the following solutions, the records that do not match IDs in ids are filtered out, unlike the solutions described in the previous section (where all records can be chosen to be preserved).

To get an ActiveRecord::Result

This is a solution to get ActiveRecord::Result with PostgreSQL 9.4+.

ActiveRecord::Result is similar to an Array of Hash.

str_sql = "select persons.* from persons INNER JOIN unnest('{#{ids.join(',')}}'::int[]) WITH ORDINALITY t(id, ord) USING (id) ORDER BY t.ord;"
Person.connection.select_all(str_sql)

Person.connection.exec_query returns the same (alias?).

To get a PG::Result

This is a solution to get PG::Result with PostgreSQL 9.4+. Very similar to above, but replace exec_query with execute (the first line is identical to the solution above):

str_sql = "select persons.* from persons INNER JOIN unnest('{#{ids.join(',')}}'::int[]) WITH ORDINALITY t(id, ord) USING (id) ORDER BY t.ord;"
Person.connection.execute(str_sql)

Answer 5

I tried the answers recommending the FIELD method on Rails6 but was encountering errors. However, I discovered that all one has to do is wrap the sql in Arel.sql().

# Make sure it's a known-safe values.
user_ids = [3, 2, 1]
# Before 
users = User.where(id: user_ids).order("FIELD(id, 2, 3, 1)")
# With warning.

# After 
users = User.where(id: user_ids).order(Arel.sql("FIELD(id, 2, 3, 1)"))
# No warning

[1] https://medium.com/@mitsun.chieh/activerecord-relation-with-raw-sql-argument-returns-a-warning-exception-raising-8999f1b9898a

Answer 6

This is most efficiently handled in SQL via ActiveRecord and not in Ruby.

ids = [3,1,6,7,12,2]
Post.where(id: ids).order("FIELD(id, #{ids.join(',')})")

Answer 7

This simple solution costs less than joining on values:

order_query = <<-SQL
  CASE persons.id 
    #{ids.map.with_index { |id, index| "WHEN #{id} THEN #{index}" } .join(' ')}
    ELSE #{ids.length}
  END
SQL
Person.where(id: ids).order(order_query)

Answer 8

With Rails 5, I've found that this approach works (with postgres, at least), even for scoped queries, useful for working with ElasticSearch:

Person.where(country: "France").find([3, 2, 1]).map(&:id)
=> [3, 2, 1]

Note that using where instead of find does not preserve the order.

Person.where(country: "France").where(id: [3, 2, 1]).map(&:id)
=> [1, 2, 3]

Answer 9

Most of the other solutions don't allow you to further filter the resulting query, which is why I like Koen's answer.

Similar to that answer but for Postgres, I add this function to my ApplicationRecord (Rails 5+) or to any model (Rails 4):

def self.order_by_id_list(id_list)
  values_clause = id_list.each_with_index.map{|id, i| "(#{id}, #{i})"}.join(", ")
  joins("LEFT JOIN (VALUES #{ values_clause }) AS #{ self.table_name}_id_order(id, ordering) ON #{ self.table_name }.id = #{ self.table_name }_id_order.id")
    .order("#{ self.table_name }_id_order.ordering")
end

The query solution is from this question.

Answer 10

Old question, but the sorting can be done by ordering using the SQL FIELD function. (Only tested this with MySQL.)

So in this case something like this should work:

Person.order(Person.send(:sanitize_sql_array, ['FIELD(id, ?)', ids])).find(ids)

Which results in the following SQL:

SELECT * FROM people
  WHERE id IN (1, 3, 5, 9, 6, 2)
  ORDER BY FIELD(id, 1, 3, 5, 9, 6, 2)

Answer 11

If you have ids array then it is as simple as - Person.where(id: ids).sort_by {|p| ids.index(p.id) } OR

persons = Hash[ Person.where(id: ids).map {|p| [p.id, p] }] ids.map {|i| persons[i] }

Answer 12

There are two ways to get entries by given an array of ids. If you are working on Rails 4, dynamic method are deprecated, you need to look at the Rails 4 specific solution below.

Solution one:

Person.find([1,2,3,4])

This will raise ActiveRecord::RecordNotFound if no record exists

Solution two [Rails 3 only]:

Person.find_all_by_id([1,2,3,4])

This will not cause exception, simply return empty array if no record matches your query.

Based on your requirement choosing the method you would like to use above, then sorting them by given ids

ids = [1,2,3,4]
people = Person.find_all_by_id(ids)
# alternatively: people = Person.find(ids)
ordered_people = ids.collect {|id| people.detect {|x| x.id == id}}

Solution [Rails 4 only]:

I think Rails 4 offers a better solution.

# without eager loading
Person.where(id: [1,2,3,4]).order('id DESC')

# with eager loading.
# Note that you can not call deprecated `all`
Person.where(id: [1,2,3,4]).order('id DESC').load

Answer 13

You can get users sorted by id asc from the database and then rearrange them in the application any way you want. Check this out:

ids = [1, 3, 5, 9, 6, 2]
users = ids.sort.map {|i| {id: i}} # Or User.find(ids) or another query

# users sorted by id asc (from the query)
users # => [{:id=>1}, {:id=>2}, {:id=>3}, {:id=>5}, {:id=>6}, {:id=>9}]

users.sort_by! {|u| ids.index u[:id]} 

# users sorted as you wanted
users # => [{:id=>1}, {:id=>3}, {:id=>5}, {:id=>9}, {:id=>6}, {:id=>2}]

The trick here is sorting the array by an artificial value: index of object's id in another array.

Answer 14

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