Rails 7 adds ActiveRecord::QueryMethods#in_order_of to return query results in a particular sequence

When working with ActiveRecord in Rails, we sometimes expect the query results in a particular sequence/order.

Let’s say we have a Rails application for Book readers, where they keep track of the books read, currently reading and the ones which they want to read.

A simple solution to build the above application is to create a UserBook model. The model will have three columns book_id, user_id, and status. status column can take any of these three distinct values read, currently_reading and to_read.

Before

To display user books in the order of to_read, currently_reading and read we might implement below code:

user = User.first

// using Arel
result = user.user_books.
           order(
             Arel.sql(
               %q(
                  case status
                  when 'to_read' then 1
                  when 'currently_reading' then 2
                  when 'read' then 3
                  end
               )
             )
           )

// using query and then arrange the records
result = user.user_books.where(status: %w[to_read currently_reading read])

// we can use the below approach or
// use dutch national flag problem solution
// https://en.wikipedia.org/wiki/Dutch_national_flag_problem

ordered_result = result.collect{ |user_book| user_book.status == "to_read" } +
                 result.collect{ |user_book| user_book.status == "currently_reading" } +
                 result.collect{ |user_book| user_book.status == "read" }

Either we have to write an Arel sql statement or iterate on the query records to map the final result as per the provided order.

After

Rails 7 adds in_order_of method to ActiveRecord::QueryMethods to resolve the above issue.

The above implementation with this new change will look as below:

user = User.first

result = user.user_books.in_order_of(:status, %w[to_read currently_reading read])

=> #<ActiveRecord::Relation [#<UserBook id: 3, user_id: 1, status: "to_read">, #<UserBook id: 4, user_id: 1, status: "to_read">, #<UserBook id: 5, user_id: 1, status: "currently_reading">, #<UserBook id: 6, user_id: 1, status: "read">]>

UserBook.in_order_of will generate the below query

SELECT "user_books".* FROM "user_books" /* loading for inspect */ ORDER BY CASE "user_books"."status" WHEN 'to_read' THEN 1 WHEN 'currently_reading' THEN 2 WHEN 'read' THEN 3 ELSE 4 END ASC LIMIT ?  [["LIMIT", 11]]

But in the case of MySQL FIELD function will be used instead of CASE.

SELECT "user_books".* FROM "user_books" /* loading for inspect */ ORDER BY FIELD("user_books"."status", 'to_read', 'currently_reading', 'read') ASC

Rails 7 added in_order_of to Enumerable which behaves similar to ActiveRecord in_order_of.

ActiveRecord in_order_of was added to make it work on ActiveRecord::Relation objects, unlike Enumerable#in_order_of that works on Enumerators.