DEV Community

JT Dev for JetThoughts LLC

Posted on • Originally published at jtway.co

Custom ordering without custom SQL with Ruby on Rails 7

Custom order SQL

The problem
It's a common case for Rails applications to have enum fields on a model like:

class Review < ApplicationRecord
  enum status: [:pending, :processing, :completed]
end
Enter fullscreen mode Exit fullscreen mode

All's good until we need to sort records by their status in a specific order. For example, on reviews page records should be ordered by status in alphabetical order, but internally in DB status values are stored as integers.
We can't just add order(status: :asc). Previously we'd have to write some custom SQL for the task:

Review.order("
  CASE status
  WHEN 2 THEN 1
  WHEN 0 THEN 2
  WHEN 1 THEN 3
  END
  ASC
")
Enter fullscreen mode Exit fullscreen mode

The solution
Ruby on Rails 7.0 provides a new query method #in_order_of which accepts as arguments a column name and an array of values in order we want the records to be ordered by:

Review.in_order_of(
  :status, [:complete, :pending, :processing]
)
Enter fullscreen mode Exit fullscreen mode

Which translates to SQL query like:

SELECT "reviews".* FROM "reviews" 
WHERE "reviews"."status" IN (2, 0, 1) 
ORDER BY CASE "reviews"."status"
  WHEN 2 THEN 1 
  WHEN 0 THEN 2 
  WHEN 1 THEN 3 
END ASC
Enter fullscreen mode Exit fullscreen mode

This works well, as long as we don't have/care about reviews with NULL as their status.
If we try to pass nil to the values array:

Review.in_order_of(
  :status, [nil, :complete, :pending, :processing]
)
Enter fullscreen mode Exit fullscreen mode

Following SQL would be created:

SELECT "reviews".* FROM "reviews" 
WHERE "reviews"."status" IN (NULL, 2, 0, 1) 
ORDER BY CASE "reviews"."status"
  WHEN NULL THEN 1
  WHEN 2 THEN 2
  WHEN 0 THEN 3
  WHEN 1 THEN 4
END ASC
Enter fullscreen mode Exit fullscreen mode

But since NULL in SQL is a special value and NULL != NULL, then this query simply won't return records with NULL as a status at all.

The improvement
Ruby on Rails 7.0.7 further improves the method: now it correctly handles the NULL values, so the resulting query would be:

SELECT "reviews".* FROM "reviews" 
WHERE (
  "reviews"."status" IN (2, 0, 1)
  OR "reviews"."status" IS NULL
)
ORDER BY CASE 
  WHEN "reviews"."status" IS NULL THEN 1
  WHEN "reviews"."status" = 2 THEN 2
  WHEN "reviews"."status" = 0 THEN 3
  WHEN "reviews"."status" = 1 THEN 4
END ASC
Enter fullscreen mode Exit fullscreen mode

Now it does exactly what we need without writing any custom SQL queries.

Top comments (0)