Sometimes, you may want to fetch records in some arbitrary order of values of a column. This tutorial illustrates how to do fetch records based on some arbitrary order of values in a rails application with PostgreSQL or MySQL as a database.

Let’s consider an example. Suppose we have a table named users with the following columns:

Consider a use-case where we want to fetch the list of users with age less than 50 in some specified order of status values as given below.

SORT_ORDER = %w(stale inactive in_progress active )

Now, there is an easy way to fetch these records from database and then perform sort in the ruby. It could be done as given below.html

User.where('age < ?', 50).all.sort_by { |user| SORT_ORDER.index(user.status) }

This shall give us the list of users with age less than 50 in the sort order specified by the constant SORT_ORDER.

But, performing operations in Ruby can be avoided if we could fetch the same result directly from the database. Let’s try to write a query for the same.

With PostgreSQL database

SELECT * FROM users
WHERE age < 50
ORDER BY case status
  WHEN 'stale' THEN 1
  WHEN 'inactive' THEN 2
  WHEN 'in_progress' THEN 3
  WHEN 'active' THEN 4
END

As we can see in the code above, we have written a case conditional expression for each status value to perform ordering as expected.

Now, we can transform this to Rails ActiveRecord scope to be used on our User model. Let’s define a scope order_by_status for this functionality.

  scope :order_by_status, lambda {
    order_clause = 'CASE status '
    SORT_ORDER.each_with_index do |value, index|
      order_clause << sanitize_sql_array(['WHEN ? THEN ? ', value, index])
    end
    order_clause << sanitize_sql_array(['ELSE ? END', SORT_ORDER.length])
    order(Arel.sql(order_clause))
  }

We are considering the custom SQL based on the values in the SORT_ORDER.

With MySQL database

MySQL has a built in function field which can be used to fetch records in arbitary order. Let’s try to use this function to fetch the same result.

SELECT * FROM users
WHERE age < 50
ORDER BY field(status, 'stale', 'inactive', 'in_progress', 'active')

Now, we can transform this to Rails ActiveRecord scope to be used on our User model. Let’s define a scope order_by_status for this functionality.

  scope :order_by_status, lambda {
    sanitized_statuses = SORT_ORDER.map{ |status| ActiveRecord::Base.connection.quote(status) }.join(', ')
    order(Arel.sql("field(status, #{sanitized_statuses})"))
  }

Usage to fetch custom ordered records

This can be used as given below.

User.where('age < ?', 50).order_by_status.all

Moreover, if you would such ordering across multiple models, you can consider moving this scope to ActiveSupport Concern.

Rails 7 in_order_of method

Rails introduces in_order_of method to ActiveRecord::QueryMethods. This allows to fetch records in the custom order as we wanted. This can be done as given below.html

User.where('age < ?', 50).in_order_of(:status, SORT_ORDER).all

This gives the same result as we have done above. Here, we don’t have to worry about making sure we build up SQL based on the database being used with the Rails application. Rails will take care of it for us.