Quite often in our Rails application, we need to retrieve records sorted in a specific order. For example, in an e-commerce application, we want to retrieve the customer orders in the order of "Completed, Cancelled, In Transit, Pending" statuses. The way to do it is to write an explicit SQL query. Starting Rails 7, this can be achieved simply with the help of the in_order_of method for ActiveRecord::QueryMethods and Enumerable.

This article explains how we can use the in_order_of method to sort data in Rails 7.

1. ActiveRecord::QueryMethods#in_order_of

Consider we have a model CustomerOrder with an attribute order_status which can take values Pending, In Transit, Completed, or Cancelled.

Assume we need to retrieve the customer orders in the following order - Completed, Cancelled, In Transit, and Pending.

Before Rails 7

The only way to achieve this is to write SQL query using CASE statement.

3.0.0 :001 > CustomerOrder.order(
3.0.0 :002 >   Arel.sql(
3.0.0 :003'>     %q(
3.0.0 :004'>       CASE order_status
3.0.0 :005'>       WHEN 'Completed' THEN 1
3.0.0 :006'>       WHEN 'Cancelled' THEN 2
3.0.0 :007'>       WHEN 'In Transit' THEN 3
3.0.0 :008'>       WHEN 'Pending' THEN 4
3.0.0 :009'>       ELSE 5 END
3.0.0 :010'>     )
3.0.0 :011 >   )
3.0.0 :012 > )
  CustomerOrder Load (2.4ms)  SELECT `customer_orders`.* FROM `customer_orders` ORDER BY
      CASE order_status
      WHEN 'Completed' THEN 1
      WHEN 'Cancelled' THEN 2
      WHEN 'In Transit' THEN 3
      WHEN 'Pending' THEN 4
      ELSE 5 END
 =>

With Rails >= 7

We can use the method in_order_of to fetch the desired result without writing a complex SQL query.

Note: The above code is for PostgreSQL OR SQLite databases and does not work with MySQL up to Rails 7.0.3.1 version as explained below. We will see below how to tweak and make it work for MySQL.

While exploring this using the MySQL database in the rails console, I encountered the following error.

3.0.0 :001 > CustomerOrder.in_order_of(:order_status, ['Completed', 'Cancelled', 'In Transit', 'Pending'])
(Object doesn't support #inspect)
 =>

Debugging the problem further revealed that it was throwing an error while constructing the SQL.

#<Arel::Visitors::UnsupportedVisitError: Unsupported argument type: String. Construct an Arel node instead.> rescued during inspection

To fix this error, we must pass quoted strings to in_order_of method to make it work with MySql. This can be achieved by using Arel::Nodes.build_quoted to create a quoted string of each array element.

3.0.0 :001 > CustomerOrder.in_order_of(:order_status, %w[Cancelled Completed].map{ |a| Arel::Nodes.build_quoted(a) })
  CustomerOrder Load (0.3ms)  SELECT `customer_orders`.* FROM `customer_orders` WHERE `customer_orders`.`order_status` IN ('Cancelled', 'Completed') ORDER BY FIELD(`customer_orders`.`order_status`, 'Completed', 'Cancelled') DESC
 => 
[#<CustomerOrder:0x00007f205078db98
  id: 4,
  name: "Shoes",
  order_status: "Cancelled",
  created_at: Tue, 17 May 2022 17:52:22.059726000 UTC +00:00,
  updated_at: Tue, 17 May 2022 17:52:22.059726000 UTC +00:00>,
 #<CustomerOrder:0x0000563be9097a88
  id: 1,
  name: "Shirt",
  order_status: "Completed",
  created_at: Tue, 17 May 2022 17:41:26.606019000 UTC +00:00,
  updated_at: Tue, 17 May 2022 17:41:26.606019000 UTC +00:00>] 
3.0.0 :002 >
Till Rails 7.0.3.1

If you have noticed the difference between the query generated by the Mysql adapter and that by PostgreSQL or SQLite ,  PostgreSQL or SQLite adapter uses CASE statement for ordering data whereas Mysql adapter uses FIELD function.

Update: The same fix was later merged to the Rails 7-0-stable branch with this PR. If we want to avoid this workaround we either have to use the Rails 7-0-stable branch or wait for releases >Rails 7.0.3.1.

2. Enumerable#in_order_of

Rails 7 has added in_order_of for Enumerable as well.

Let us follow the example below to understand it. Suppose, we have Item enumerable with id and name keys. We can fetch the records of enumerable in the order of id or name field. 

3.0.0 :001 > Item = Struct.new(:id, :name)
 => Item 
3.0.0 :002 > items = [Item.new(1, "T-shirt"), Item.new(2, "Pant"), Item.new(4, "Shoes"), Item.new(6, "Mat")]
 => [#<struct Item id=1, name="T-shirt">, #<struct Item id=2, name="Pant">, #<struct Item id=4, name="Shoes">, #<struct Item id=6, name... 
3.0.0 :003 > items.in_order_of(:id, [6, 1, 2, 4])
 => [#<struct Item id=6, name="Mat">, #<struct Item id=1, name="T-shirt">, #<struct Item id=2, name="Pant">, #<struct Item id=4, name="Shoes">]

We can pass the range as well and it returns records with id present in that range.

3.0.0 :013 > items.in_order_of(:id, 2..6)
 => [#<struct Item id=2, name="Pant">, #<struct Item id=4, name="Shoes">, #<struct Item id=6, name="Mat">]

When we pass values and any of the values are not present in enumerable, it returns only matching records. In the below example 5 will get ignored and other matching records will be returned in the desired order.

3.0.0 :004 > items.in_order_of(:id, [6, 5, 1, 2])
 => [#<struct Item id=6, name="Mat">, #<struct Item id=1, name="T-shirt">, #<struct Item id=2, name="Pant">]

References:

  1. Add ActiveRecord::QueryMethods#in_order_of
  2. Enumerable#in_order_of
  3. SO: Rails ActiveRecord Arels: Unsupported argument type: String. Construct an Arel node instead