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') ASCRails 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.
