Sorting ActiveRecord Records by Array

Question

I'm using postgres DB & I have an array of product ids: [5, 4, 1, 2] then I invoked

products = Product.where(:id => ids)

Now result products sort after invoking products.map(&:id) is [4, 1, 2, 5]

How can I sort the products using same sort of ids array?

SteveTurczyn · Accepted Answer · 2018-02-21 08:42:41Z

5

I'm not in a position to test but this should work...

ids = [5, 4, 1, 2]

products = Product.where(id: ids).order('array_position(?, products.id)', ids)

answered Feb 21, 2018 at 8:42

SteveTurczyn

37k6 gold badges46 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

nice db implementation, not sure which is best when we only get low number of rows but cool anyway!

corrected answer products = Product.where(id: ids).order("array_position(ARRAY[#{ids.join(',')}], products.id)")

products.id::int. Rails's ids are big int now

apneadiving · Accepted Answer · 2018-02-21 08:42:17Z

2

This would do because you have few rows:

ids = [5, 4, 1, 2] 
products = Product.where(id: ids)
products = ids.map {|id| products.select {|p| p.id == id }  }

answered Feb 21, 2018 at 8:42

apneadiving

116k26 gold badges225 silver badges215 bronze badges

@apneadiving thanks but I expect to get ActiveRecord::Relation but this will return an array.

sure thing @SteveTurczyn solution is what you need then :)