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:
- You think to yourself: “Should I write an
order by case when
statement? Maybe I’ll just add a little bit of SQL…” - 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:
- The
current_user
’s comments should come first; - 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: