Boring Rails

Tip: Sorting ActiveRecord results by enum values (in SQL)

:fire: Tiny Tips

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

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.

# "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
    accepted: 0,
    hold: 1,
    submitted: 2,
    draft: 3,
    rejected: 4,
    canceled: 5

  def 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).


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)
  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

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.

  .in_order_of(:status, %w[accepted hold submitted draft rejected canceled])

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]) }


Additional Resources

Rails API Docs: ActiveRecord::QueryMethods#in_order_of

If you like these tips, you'll love my Twitter account. All killer, no filler.