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

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!

possible duplicate of http://stackoverflow.com/questions/801824/clean-way-to-find-activerecord-objects-by-id-in-the-order-specified — Sonalkumar sute, Mar 17 '15 at 10:35

score 24 · Answer 1 · edited May 23 '17 at 10:31

24

With reference to here, for postgresql,

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

edited May 23 '17 at 10:31

Community

1
1

answered Nov 28 '15 at 18:54

Vic

1,512
17
25

score 12 · Answer 2 · answered Mar 20 '17 at 11:52

12

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

answered Mar 20 '17 at 11:52

fengd

7,551
3
41
44

2

This answer works great - my comment is not a criticism, just info incase someone else runs into the same issue. Be aware that the `sort_by` used in the answer returns an `array`, not an `ActiveRecord_Relation` object that some gems (like the pagination gem I was using) require to work properly. The answer below by `Vic` worked for me since I was using postgres and needed an ordered AR relation. – Nathan Beck Aug 31 '17 at 19:56
1

@NathanBeck it is. `sort_by` itself is an `Array` method – fengd Sep 01 '17 at 09:50
Exactly what I was looking for thanks for the answer. You Rocks!!!! – Chetan Kumar Aug 24 '21 at 14:30

score 10 · Answer 3 · answered Mar 17 '15 at 10:34

10

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

answered Mar 17 '15 at 10:34

BroiSatse

44,031
8
61
86

Note that, if your column data is string or text, wrap your inputted value within escaped double quotes first. ie `#{ids.map{|x| "\"#{x}"\"}}.join(',')` . Otherwise, you will still get the id sorted result. – Dylandy Aug 19 '21 at 03:05

score 1 · Answer 4 · answered Mar 17 '15 at 10:42

1

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

answered Mar 17 '15 at 10:42

lx00st

1,566
9
17

score 1 · Answer 5 · answered Jan 09 '19 at 23:39

1

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

answered Jan 09 '19 at 23:39

mtc

21
5

common column name "id" appears more than once in left table – JosephK Nov 04 '20 at 15:23

score 0 · Answer 6 · edited Jul 16 '15 at 18:49

0

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

edited Jul 16 '15 at 18:49

user2031423

347
4
7

answered Mar 17 '15 at 10:34

Emu

5,763
3
31
51

score 0 · Answer 7 · answered Feb 07 '22 at 09:40

0

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

answered Feb 07 '22 at 09:40

BroiSatse

44,031
8
61
86

1

FYI: the question is tagged `ruby-on-rails-4` and the sorting behavior was introduced in Rails 5. – Stefan Feb 07 '22 at 12:07

score -1 · Answer 8 · answered Nov 07 '19 at 02:45

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)

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

8 Answers8

Linked