Rails, ActiveRecord, query id in array of ints, ke

Question

I am thinking about the best solution for a problem. Let's say that we have a list of ids of ActiveRecord model:

ids = [1, 100, 5, 30, 4, 2, 88, 44]

Then I would like to make query that selects all users for example with ids from the list but to keep the order. If I do

User.where(id: ids)

the response will be a list of users with asc order by id, but I want the order to be the same as in the array.

What do you think that it's the best solution here? Select all users and then to manipulate the list of ActiveRecord objects? Maybe there is a more clever way to do that.

Thanks!

Answer 1

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

Answer 2

Another possibility for Postgres (9.4 or later):

ordered_ids = [1, 100, 5, 30, 4, 2, 88, 44]
User.joins("join unnest('{#{ordered_ids.join(',')}}'::int[]) WITH " \
           "ORDINALITY t(id, ord) USING (id)").reorder('t.ord')

Notice that the reorder is extremely important.

Solution based on https://stackoverflow.com/a/35456954

Answer 3

if you want to get a result of Model::ActiveRecord_Relation

order(Arel.sql("field(id, ids.join(', ') asc"))

Arel.sql is required to prevent the message in log:

Dangerous query method (method whose arguments are used as raw SQL) called with non-attribute argument(s)

Answer 4

If you are using MySQL, you can use FIELD to order results:

class User < ActiveRecord::Base
  def self.find_in_order(ids)
    self.where(id: ids).order("FIELD(id, #{ids.join(',')})")
  end
end

User.find_in_order([1, 100, 5, 30, 4, 2, 88, 44])

Answer 5

If you are using Postgres you can use intarray

class User < ActiveRecord::Base
  def self.find_in_order(ids)
    self.where(id: ids).order("idx(array[#{ids.join(',')}], id)")
  end
end

you should init module first

CREATE EXTENSION intarray

Answer 6

With reference to here, for postgresql,

User.where(id: ids).order("position(id::text in '#{ids.join(',')}')")