Tip: Sorting ActiveRecord results by enum values (in SQL)
Rails enums are a great way to model things like a status on an ActiveRecord model. They provide a set of human-readable methods while storing the result as an integer in the database.
class JobSubmission < ApplicationRecord
enum status: {
draft: 0,
submitted: 1,
hold: 2,
rejected: 3,
accepted: 4,
canceled: 5
}
end
It is highly recommended to use a Hash to explicitly define the enum values – otherwise Rails will use the index of the enum value when storing it to the database. If you were were to change the order or remove options, you would break the reference mapping.
Out of the box, you can sort by the enum value like any other column and it will use the value.
JobSubmission.all.order(:status)
# "SELECT \"job_submissions\".* FROM \"job_submissions\" ORDER BY \"job_submissions\".\"status\" ASC
This would return JobSubmission results in ascending order, so first draft, then submitted, hold, etc.
But what if you wanted sort the results differently? You might be tempted to do something like this:
class JobSubmission < ApplicationRecord
STATUS_SORT = {
accepted: 0,
hold: 1,
submitted: 2,
draft: 3,
rejected: 4,
canceled: 5
}
def status_sort_value
STATUS_SORT[status]
end
end
JobSubmission.all.sort(&:status_sort_value)
But this will do the sorting in Ruby, instead of in the database, which can have bad performance for large result sets.
Instead you can use the in_order_of to specify the sort order of the enum values. And the best part? The sorting will happen in SQL (using a CASE command under-the-hood).
Usage
To sort the JobSubmission records by the status enum value in a different order, you can use:
JobSubmission.all.in_order_of(:status, %w[accepted hold submitted draft rejected canceled])
The first argument is the column to sort by and the second argument is an ordered array of values.
The SQL generated for this query will be:
SELECT "job_submissions".*
FROM "job_submissions"
WHERE "job_submissions"."status" IN (4, 2, 1, 0, 3, 5)
ORDER BY
CASE "job_submissions"."status"
WHEN 4 THEN 1
WHEN 2 THEN 2
WHEN 1 THEN 3
WHEN 0 THEN 4
WHEN 3 THEN 5
ELSE 6
END ASC
You can also add additional sorting to the relation. For instance, you would probably want to sort records by the status and then maybe alphabetically by the name column to break ties.
JobSubmission.all
.in_order_of(:status, %w[accepted hold submitted draft rejected canceled])
.order(:name)
Extra notes
You important caveat is that in_order_of also adds a WHERE/IN clause for the enum values. So only records with enum values that you specify when calling in_order_of will be returned. This is somewhat surprising and something to watch out for if you are frequently changing the enum values.
You will probably want to codify the sort order in the model as a scope to avoid having to repeat the ordering in multiple places.
class JobSubmission < ApplicationRecord
scope :in_status_order, -> { in_order_of(:status, %w[accepted hold submitted draft rejected canceled]) }
end
JobSubmission.in_status_order.order(:name)
Additional Resources
Rails API Docs: ActiveRecord::QueryMethods#in_order_of