Rails drops records not listed in QueryMethods#in_order_of

Rails 7 has bought in many upgrades to the in_order_of method the past year. From adding it to Enumerable to porting over to ActiveRecord, the method has proved to be very useful.

Before

However, there are still some quirks. Let’s look at the enumerable example.

> User = Struct.new(:id, :name)
=> User

> users = [User.new(1, "Alex"), User.new(6, "Sam"), User.new(4, "Roy")]
=> [#<struct User id=1, name="Alex">, #<struct User id=6, name="Sam">, #<struct User id=4, name="Roy">]

# ordering by all available IDs
> users.in_order_of(:id, [6, 1, 4])
=> [#<struct User id=6, name="Sam">, #<struct User id=1, name="Alex">, #<struct User id=4, name="Roy">]

# ordering by a subset of available IDs
> users.in_order_of(:id, [6, 1])
=> [#<struct User id=6, name="Sam">, #<struct User id=1, name="Alex">]

It drops records not part of the order list. Let’s look at an ActiveRecord example.

> User.in_order_of(:id, [1, 5, 3]).pluck(:id)
=> [1, 5, 3, 2, 4, 6, 7, 8, 9]

So this does not drop records not part of the list! This can cause many inconsistencies in working.

It is because internally, the query method generates relevant SQL using CASE statements.

> User.in_order_of(:id, [1, 5, 3]).to_sql
=> "SELECT "users".* FROM "users" ORDER BY CASE "users"."id" WHEN 1 THEN 1 WHEN 3 THEN 2 WHEN 5 THEN 3 ELSE 4 END ASC, "users"."id" ASC

However, because this functionality is built into MySQL in the form of the FIELD function, that connection adapter will generate the following SQL instead:

SELECT "users".* FROM "users" ORDER BY FIELD("users"."id", 1, 5, 3) DESC

So many inconsistencies!

After

Rails now drops records not listed in QueryMethods#in_order_of.

> User.in_order_of(:id, [1, 5, 3]).to_sql
"SELECT "users".* FROM "users" ORDER BY FIELD("users"."id", 1, 5, 3) WHERE "users"."id" IN (1, 5, 3)"

A clever and simple fix using SQL’s built-in IN keyword.

> User.in_order_of(:id, [1, 5, 3]).pluck(:id)
=> [1, 5, 3]