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

问题:

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!

回答1:

With reference to here, for postgresql,

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

回答2:

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])

回答3:

regard less of MySQL and Postgresql, if you have a small size of ids,

User.where(id: ids).sort_by { |u| ids.index(u.id) }

回答4:

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

回答5:

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

回答6:

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

回答7:

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)