records in a custom order.md

Fetch records in a custom order with ActiveRecord in Rails.md

SORT_ORDER = %w(stale inactive in_progress active )

Rails <7

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

PG Query + Scope

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

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

MySQL Query + Scope

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

Rails 7+

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