Ruby
Return Results Using a Specific Order in Rails
When passing an array to an attribute of a where it will produce an IN query. However, the order may not be represented in the returned records by the passed array.
Procedure.where(id: [4,2,6])
# SELECT "procedures".* FROM "procedures" WHERE "procedures"."id" IN (4, 2, 6)
=>
[#<Procedure:0x00007fb735b0dbc0 id: 2 ...>,
#<Procedure:0x00007fb735af7758 id: 4 ...>,
#<Procedure:0x00007fb735af7690 id: 6 ...>]
This outcome can be confusing due to find returning the records ordered by the collection passed.
Procedure.find([4,2,6])
# SELECT "procedures".* FROM "procedures" WHERE "procedures"."id" IN (4, 2, 6)
=>
[#<Procedure:0x00007fb735b0dbc0 id: 4 ...>,
#<Procedure:0x00007fb735af7758 id: 2 ...>,
#<Procedure:0x00007fb735af7690 id: 6 ...>]
Note that the queries are identical with no actual ordering as part of the query, however, the find method is ordering the result in ruby after the query returns results.
Rails 7 will introduce in_order_of as a new query method for ActiveRecord.
Just provide the column to order and a collection declaring the order.
Procedure.where(id: [4,2,6]).in_order_of(:id, [4,2,6])
# SELECT "procedures".* FROM "procedures" WHERE "procedures"."id" IN (4, 2, 6) ORDER BY CASE "procedures"."id" WHEN 4 THEN 1 WHEN 2 THEN 2 WHEN 6 THEN 3 ELSE 4 END ASC
=>
[#<Procedure:0x00007fb735b0dbc0 id: 4 ...>,
#<Procedure:0x00007fb735af7758 id: 2 ...>,
#<Procedure:0x00007fb735af7690 id: 6 ...>]
This new method allows us to order our results explicitly. This query was produced using PostgreSQL but, the method is implemented on ActiveRecord's abstract adapters. That means it'll be available for most, if not all, supported databases. Your particular adapter will utilize a CASE statement or a built-in function.
Photo by @brett_jordan
