Using array_position to Sort ActiveRecord Records By Array

ruby-on-rails-guide

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(',')})")
Share on twitter
Twitter
Share on telegram
Telegram
Share on facebook
Facebook
Share on linkedin
LinkedIn
Share on email
Email

3 thoughts on “Using array_position to Sort ActiveRecord Records By Array”

  1. WHAT TO KNOW BEFORE BUYING MINA?
    Alrighty, in previous articles we figured out the functionality.
    Let’s now take a look at the advantages of such a blockchain system, besides its size.
    The first advantage of the Mina protocol is the high confidentiality of user data.
    Other blockchains, like Ethereum, do blockchain computations.
    Source: https://cryptoine.com/how-does-mina-work-exactly/

Leave a Comment

Your email address will not be published. Required fields are marked *