You’re working on a feature and someone tells you that one of your models needs to be ordered based on some complicated rule. You realize you’ll need to write a complex order by query…

Here’s how it usually goes:

  1. You think to yourself: “Should I write an order by case when statement? Maybe I’ll just add a little bit of SQL…”
  2. Two minutes later, you end up with a bunch of nested case when statements, subqueries, common table expressions, angry statements, shouting, and a whole lot of tears. 😤

If you’ve ever tried to write a complicated order by statement with ActiveRecord, you probably know that it’s kind of painful, and you end up with ugly-looking code. That’s not what you want, right?

That sort of query quickly becomes unreadable as you add more and more cases to it.

Most examples and tutorials don’t even mention that you might need to sanitize your query… Not cool!

It’s okay to write raw SQL queries sometimes, but for this type of problem, let’s try something different, shall we?

I want to show you a better way: use Arel to generate the statements for you.

Follow along and you’ll learn how to do it.

An example of when to use an order by case statement with Ruby with Rails

Let’s suppose you have a simple schema containing users, posts and comments:

ActiveRecord::Schema.define do
  create_table :posts, force: true do |t|
  end

  create_table :users, force: true do |t|
    t.string :country
  end

  create_table :comments, force: true do |t|
    t.integer :post_id
    t.integer :user_id
  end
end

The models would look something like this:

class Post < ActiveRecord::Base
  has_many :comments
end

class User < ActiveRecord::Base
  has_many :comments
end

class Comment < ActiveRecord::Base
  belongs_to :post
  belongs_to :user
end

Normally, you’d order a post’s comments by their creation date. That’s the simple case.

However, the simple case is seldom enough. Let’s say you need to order comments by something more complicated than that:

  1. The current_user’s comments should come first;
  2. Followed by comments created by people who live in the same country as the current_user.

Your first instinct might be to write a raw SQL query. See how this is done on the next section.

How to write an order by case statement with raw SQL

If you try to do a simple raw SQL query, you could write an order-by case stamement based on the user’s id and country.

However, if you allow people to store any type of string on their user.country field, you would also need to sanitize the query to prevent SQL injections.

Let me tell you something: sanitizing case-statements is annoying, specially for order by statements.

Here’s how that would look like:

# sad-face
case_statement = <<-SQL.squish
   CASE
      WHEN users.id = ? THEN 1
      WHEN users.country = ? then 2
      ELSE 3
   END
SQL

# country could be anything, so let's sanitize the query:
sanitized_case_statement = ActiveRecord::Base.sanitize_sql_array(
  [case_statement, user.id, user.country]
)

post.comments
    .joins(:user, :post)
    .order(
      Arel.sql(sanitized_case_statement)
    )

Which is not the cleanest-looking code.

If you’ve read this far, you’re probably thinking: “Is there a better way?” Absolutely! Enter Arel.

How to Write a Clean Order By Case Statement with Arel

Arel supports case-statements, a not well-known feature.

Use it to generate a complex order by case statement query. Better yet, you won’t need to worry about sanitization.

The idea is to leverage Arel::Nodes::Case to build the SQL expression. Here’s how:

    users = User.arel_table
    same_user = users[:id].eq(current_user.id)
    same_country = users[:country].eq(current_user.country)

    case_statement = Arel::Nodes::Case.new
                                     .when(same_user).then(1)
                                     .when(same_country).then(2)
                                     .else(3)

    post.comments.joins(:user, :post).order(case_statement)

Much cleaner, right?

You could even write subqueries to expose more information and add them to your case-statement.

Then, you can break things down into scopes, or refactor pieces of the query into a Query Object. That gives you more flexibility and the code will be a little easier to extend and maintain.

Take a look at this gist to see the full example plus tests if you’d like to try it out.

And there you have it: a clean way to write case-when statements on complex order_by queries with ruby code. No need to write crazy raw SQL queries any longer! Enjoy!


Did you like this article? You're gonna love these other ones: