Using array_position to Sort ActiveRecord Records By Array

In Ruby on Rails, you may face the issue when you try to use .where with array but your expected result is sorted by your input array. Because in Rails the result of .where is sorted by id in default.

In this blog post, I’m going to share quick tips by using array_position to get your result sorted by input array.

Here is the data we prepared for demonstration of using array_position. A table called fruits with five different of fruit.

#<ActiveRecord::Relation [
  #<Fruit id: 1, name: "Apple", quantity: 20, created_at: "2021-09-12 16:33:55", updated_at: "2021-09-12 16:33:55">,
  #<Fruit id: 2, name: "Orange", quantity: 10, created_at: "2021-09-12 16:34:07", updated_at: "2021-09-12 16:34:07">,
  #<Fruit id: 3, name: "Watermelon", quantity: 30, created_at: "2021-09-12 16:34:32", updated_at: "2021-09-12 16:34:32">,
  #<Fruit id: 4, name: "Banana", quantity: 40, created_at: "2021-09-12 16:34:49", updated_at: "2021-09-12 16:34:49">,
  #<Fruit id: 5, name: "Kiwi Fruit", quantity: 50, created_at: "2021-09-12 16:35:05", updated_at: "2021-09-12 16:35:05">
]>

First, we are using this ruby code to query these three fruits. Banana, Kiwi Fruit and Orange.
Fruit.where(name: ["Banana", "Kiwi Fruit", "Orange"])
and here is the result

#<ActiveRecord::Relation [
  #<Fruit id: 2, name: "Orange", quantity: 10, created_at: "2021-09-12 16:34:07", updated_at: "2021-09-12 16:34:07">,
  #<Fruit id: 4, name: "Banana", quantity: 40, created_at: "2021-09-12 16:34:49", updated_at: "2021-09-12 16:34:49">,
  #<Fruit id: 5, name: "Kiwi Fruit", quantity: 50, created_at: "2021-09-12 16:35:05", updated_at: "2021-09-12 16:35:05">
]>

You can see that even your input array is ["Banana", "Kiwi Fruit", "Orange"] but the result is sorted by ID. Is there a way to sort the result by the input array? You can use array_position. We can just make a smaller change to achieve.

fruits = ["Banana", "Kiwi Fruit", "Orange"]

# fruits.map { |x| "'#{x}'" }.join(',') used to convert array to string 'Banana', 'Kiwi Fruit', 'Orange'
Fruit.where(name: fruits).order("array_position(ARRAY[#{fruits.map { |x| "'#{x}'" }.join(',')}], name::TEXT)")

Here is the ActiveRecord result

#<ActiveRecord::Relation [
  #<Fruit id: 4, name: "Banana", quantity: 40, created_at: "2021-09-12 16:34:49", updated_at: "2021-09-12 16:34:49">,
  #<Fruit id: 5, name: "Kiwi Fruit", quantity: 50, created_at: "2021-09-12 16:35:05", updated_at: "2021-09-12 16:35:05">,
  #<Fruit id: 2, name: "Orange", quantity: 10, created_at: "2021-09-12 16:34:07", updated_at: "2021-09-12 16:34:07">
]>

as you can see you can use array_position to sort the record by array.

What if I’m using MySQL?

you can use field to sort the result

fruits = ["Banana", "Kiwi Fruit", "Orange"]
Fruit.where(name: fruits).order("field(name, #{fruits.join(',')})")