'Rails, ActiveRecord, query id in array of ints, keep order of passed array

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!

ruby-on-railsruby-on-rails-4


Solution 1:[1]

With reference to here, for postgresql, 

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

Solution 2:[2]

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

Solution 3:[3]

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

Solution 4:[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

Solution 5:[5]

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

Solution 6:[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

Solution 7:[7]

If you're searching by primary key you can use find, which accepts an array and guarantees order.

User.find(ids)

Few "downsides"/"extra features":

  • it will return an array instead of relation, so it needs to be called at the very end of your chaining.
  • it will raise ActiveRecord::NotFound exception if any of the given ids is not present in the database

Solution 8:[8]

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)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Community
Solution 2 fengd
Solution 3 BroiSatse
Solution 4 lx00st
Solution 5 user2031423
Solution 6 mtc
Solution 7 BroiSatse
Solution 8 ogelacinyc

Related Questions