CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-4
user1107922
user1107922

Reputation: 610

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!

Upvotes: 25

Views: 8997

Answers (8)

BroiSatse
BroiSatse

Reputation: 44725

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

Upvotes: 1

ogelacinyc
ogelacinyc

Reputation: 1372

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)

Upvotes: -1

mtc
mtc

Reputation: 41

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

Upvotes: 3

fengd
fengd

Reputation: 7579

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

Upvotes: 12

Vic
Vic

Reputation: 1542

With reference to here, for postgresql, 

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

Upvotes: 25

Emu
Emu

Reputation: 5905

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

Upvotes: 1

lx00st
lx00st

Reputation: 1596

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

Upvotes: 1

BroiSatse
BroiSatse

Reputation: 44725

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

Upvotes: 10

Related Questions