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 whenstatement? Maybe I’ll just add a little bit of SQL…"
- Two minutes later, you end up with a bunch of nested
case whenstatements, 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.
Let’s suppose you have a simple schema containing
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:
current_user’s comments should come first;
- Followed by comments created by people who live in the same country as the
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
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!