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
Consider we have a model
CustomerOrder with an attribute
order_status which can take values
Assume we need to retrieve the customer orders in the following order -
In Transit, and
Before Rails 7
The only way to achieve this is to write SQL query using
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
22.214.171.124 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.
If you have noticed the difference between the query generated by the
Mysql adapter and that by
SQLite adapter uses
CASE statement for ordering data whereas
Mysql adapter uses
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
Rails 7 has added
Enumerable as well.
Let us follow the example below to understand it. Suppose, we have
Item enumerable with
name keys. We can fetch the records of enumerable in the order of
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">]