Sorting elegantly in Rails with in_order

Prior to rails 7 sorting a column in a custom order was not straightforward, typically requiring raw SQL or complex workarounds; the in_order_of method offers a more elegant and efficient solution for custom sorting without resorting to raw SQL.

Custom Sorting Without `in_order_of`

Let’s take this example: You have a Task model with different statuses:

class Task < ApplicationRecord
  enum status: { pending: 0, in_progress: 1, completed: 2, archived: 3 }
end

If you want tasks sorted in the order ['in_progress', 'pending', 'completed', 'archived']. prior to rails 7 there are several ways you could achieve this:

1. Raw SQL

Task.order(Arel.sql("FIELD(status, 1, 0, 2, 3)"))

2. Using `CASE WHEN` in SQL

Task.order(Arel.sql("CASE status
  WHEN 1 THEN 1  /* in_progress */
  WHEN 0 THEN 2  /* pending */
  WHEN 2 THEN 3  /* completed */
  WHEN 3 THEN 4  /* archived */
END"))

3. Sorting in Ruby

custom_order = ['in_progress', 'pending', 'completed', 'archived']
Task.all.sort_by { |task| custom_order.index(task.status) }

While all the above approaches are valid, they all have drawbacks:

The SQL-based solutions depend on database-specific syntax and are difficult to read because of the enum numerical values in the database, while Ruby sorting, loads all records into memory, impacting performance and making it inefficient for large data sets.

The Rails 7.1 Solution: `in_order_of`

Rails 7.1 introduces in_order_of, making custom sorting effortless and database-agnostic. Here’s how you do it:

Task.in_order_of(:status, [:in_progress, :pending, :completed, :archived])

How `in_order_of` works

The in_order_of method constructs a CASE statement or utilizes built-in database functions to enforce the specified order, For the example above, it would generate SQL similar to:

SELECT "tasks".* FROM "tasks" ORDER BY CASE "tasks"."status"
  WHEN 1 THEN 0   /* in_progress */
  WHEN 0 THEN 1   /* pending */
  WHEN 2 THEN 2   /* completed */
  WHEN 3 THEN 3   /* archived */
  ELSE 4          /* any other values */
END

Pros

Database-agnostic: Works across different database engines without requiring database-specific syntax.
Rails-native: Integrates seamlessly with the Active Record query interface and can be chained with other methods like where, limit,order etc.
Type-handling: Automatically handles different data types appropriately.
Preserves lazy loading: Keeps the query lazy-loaded until results are needed, unlike Ruby-side sorting.

Cons

Performance overhead: Generates a CASE WHEN SQL statement internally, which can be expensive for large ordered lists.
Index limitations: Database indexes aren’t effectively utilized with these custom order statements.
SQL complexity: The generated SQL can be complex and harder to optimize.
Missing values handling: Records with values not in the specified list are typically appended at the end, sometimes in unpredictable order.

Conclusion

If you find yourself reaching for raw SQL for custom sorting in Rails, in_order_of can help achieve this easily. While this method can have disadvantages in particular cases, as highlighted above, when it fits your use case, it can greatly improve the readability and maintainability of your code.

Sorting elegantly in Rails with in_order_of