rails_7 Published on  16 February 2023 16 February 2023 • 3 min read

Improvements to in_order_of active record query method in Rails 7.1

Rails 7 has introduced the in_order_of method which allows to sort records in a specific order. There is a detailed explanation of it in the article Rails 7 adds in_order_of for ActiveRecord::QueryMethods and Enumerable. This is a follow-up article to that one, in which we will explore how the in_order_of method has been improved in Rails 7.1.

Allows to order by string column name

In Rails 7, if a column name is passed to the in_order_of method as a string, it raises the following error:

Loading development environment (Rails 7.0.3)
3.0.0 :001 > CustomerOrder.in_order_of('name', ['Shirt', 'Jeans'])
/usr/share/rvm/gems/ruby-3.0.0/gems/activerecord-7.0.3/lib/active_record/relation/query_methods.rb:459:in `in_order_of': undefined method `in' for "name":String (NoMethodError)                                             
Did you mean?  in?           

It has been fixed in PR#45971. From Rails 7.1 onwards, we are allowed to pass the column name as a string value to the in_order_of method.

Loading development environment (Rails 7.1.0.alpha)
3.0.3 :001 > CustomerOrder.in_order_of("name", ['Shirt', 'Jeans'])
  CustomerOrder Load (0.5ms)  SELECT "customer_orders".* FROM "customer_orders" WHERE "customer_orders"."name" IN ('Shirt', 'Jeans') /* loading for pp */ ORDER BY CASE WHEN "customer_orders"."name" = 'Shirt' THEN 1 WHEN "customer_orders"."name" = 'Jeans' THEN 2 END ASC LIMIT ?  [["LIMIT", 11]]                                                            
 =>                                                                   
[#<CustomerOrder:0x000055cb391a3600                                   
  id: 1,
  name: "Shirt",
  order_status: "Completed",
  created_at: Mon, 23 Jan 2023 15:42:08.473523000 UTC +00:00,
  updated_at: Mon, 23 Jan 2023 15:42:08.473523000 UTC +00:00>,
 #<CustomerOrder:0x000055cb391a3538
  id: 2,
  name: "Jeans",
  order_status: nil,
  created_at: Mon, 23 Jan 2023 15:43:03.940857000 UTC +00:00,
  updated_at: Mon, 23 Jan 2023 15:43:03.940857000 UTC +00:00>]    

Works with nil values

In Rails 7, if nil is passed to the in_order_of method, the following SQL is generated:

Loading development environment (Rails 7.0.3)
3.0.0 :001 > CustomerOrder.in_order_of(:order_status, [nil, 'Completed']).to_sql
 => "SELECT \"customer_orders\".* FROM \"customer_orders\" WHERE \"customer_orders\".\"order_status\" IN (NULL, 'Completed') ORDER BY CASE \"customer_orders\".\"order_status\" WHEN NULL THEN 1 WHEN 'Completed' THEN 2 WHEN ELSE 3 END ASC" 

As we can see from the example above, the CASE statement adds a check for WHEN NULL THEN, but NULL != NULL in SQL. As a result, when ordering, records with NULL values are ignored, and those records are excluded from the final result.

In Rails 7.1, The PR#45670 has added a fix to generate SQL WHEN \"customer_orders\".\"order_status\" IS NULL for nil values, which sort records correctly and return records with nil value in the result.

Loading development environment (Rails 7.1.0.alpha)
3.0.3 :001 >  CustomerOrder.in_order_of(:order_status, [nil, 'Completed'])
  CustomerOrder Load (0.3ms)  SELECT "customer_orders".* FROM "customer_orders" WHERE ("customer_orders"."order_status" IN ('Completed') OR "customer_orders"."order_status" IS NULL) /* loading for pp */ ORDER BY CASE WHEN "customer_orders"."order_status" IS NULL THEN 1 WHEN "customer_orders"."order_status" = 'Completed' THEN 2 END ASC LIMIT ?  [["LIMIT", 11]]              
 =>                                                                                          
[#<CustomerOrder:0x000055cb3822b308                                                          
  id: 2,                                                                                     
  name: "Jeans",                                                                              
  order_status: nil,                                                                         
  created_at: Mon, 23 Jan 2023 15:43:03.940857000 UTC +00:00,                                
  updated_at: Mon, 23 Jan 2023 15:43:03.940857000 UTC +00:00>,                               
 #<CustomerOrder:0x000055cb3822b240                                                          
  id: 1,                                                                                     
  name: "Shirt",                                                                             
  order_status: "Completed",                                                                 
  created_at: Mon, 23 Jan 2023 15:42:08.473523000 UTC +00:00,                                
  updated_at: Mon, 23 Jan 2023 15:42:08.473523000 UTC +00:00>]

Using ORDER BY CASE for all

In Rails 7, in_order_of uses the special order field generation, i.e. ORDER BY FIELD for the MySQL adapter. This does not add any performance improvement other than a simplified query, so it has been replaced by ORDER BY CASE.

From Rails 7.1 onwards, all database adapters will now use ORDER BY CASE. The PR#45670 has added this fix, the same PR which has added fix for in_order_of to work with nils.

References:

Supriya Laxman Medankar

Software Engineer at Kiprosh.com