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.
Supriya Laxman Medankar
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.