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