Building a Leaderboard Query with ActiveRecord

Recently, I was asked how to structure a query for a game leaderboard. The leaderboard needs to look something like this:

<ol>
<% @ranked_users.each do |user| %>
  <li><%= user.high_score %> - <%= user.name %></li>
<% end %>
</ol>

In SQL, you could write a query like this:

SELECT u.*, max(SELECT final_score FROM games WHERE user_id = u.id) as high_score
FROM users u
ORDER BY high_score DESC;

But how would you translate that query to ActiveRecord? The solution involves leveraging a little-known feature of ActiveRecord: dynamic model attributes.

Let’s assume a User model like this:

class User < ActiveRecord::Base
  has_many :games

  def high_score
    games.order(final_score: :desc).first
  end
end

The n+1 query problem

You definitely don’t want to do this:

@ranked_users = Users.all.sort_by(&:high_score)

Since high_score will issue a query to fetch the user’s game records. You’ll end up issuing an extra query for each user returned by your initial query. So how can we avoid this pitfall?

Using includes doesn’t cut it

Whenever you have an n+1 query problem, ActiveRecord::Base#includes should be your first line of defense. Using this method will cause ActiveRecord to query for records related to the base query all at once, rather than one at a time.

However, in our case, since high_score involves ordering the records, the records cache created by includes will miss, and we’ll still issue n+1 queries:

# NOTE: Still issues n+1 queries
@ranked_users = Users.includes(:games).sort_by(&:high_score)

We can even force ActiveRecord to order the users in the order we want by joining in the game records and ordering by their score:

@ranked_users = Users.includes(:games).joins(:games).order("games.score")

But we’ll still trigger another query every time we call ‘ high_score method in the view.

So what can we do?

Dynamic SQL attributes instead to the rescue!

Ever wonder why you don’t need to declare attributes in your model classes? No, it’s not because of the db/schema.rb file. It’s because ActiveRecord actually dynamically defines methods on your model instances based on what columns the database returns from your query.

We can use this fact to finally implement our leaderboard query:

@ranked_users =
  User.select("*, max(SELECT max(score) FROM games WHERE user_id = users.id) AS highest_score")
      .order("highest_score")

This structure allows us to issue the exact SQL query we began with, so we avoid the n+1 query problem while still retrieving the high score for each user! And yes, each user instance in @ranked_users has a method highest_score that will return that user’s high score. So, with a simple modification to our view, we’ve got all the data we need for our view in one simple query:

<ol>
<% @ranked_users.each do |user| %>
  <li><%= user.highest_score %> - <%= user.name %></li>
<% end %>
</ol>

Two gotchas to note:

1. The alias (here, highest_score) must not conflict with existing methods on the the model. For example, if we used the alias high_score, then the User#high_score method would override our subquery result, and we’d still issue extra queries.
2. The argument to order must be a string in this case. A symbol will be prefixed with the users table name in the resulting query, and will fail.

There are many other uses for these dynamic model attributes from SQL beyond subquery results. You can write any valid SQL you want inside most of ActiveRecord’s query methods. This in combination with the dynamic nature of the library provides a powerful and flexible tool in your Ruby toolchain.