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)
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?
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:
- 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_scoremethod would override our subquery result, and we’d still issue extra queries.
- The argument to
ordermust be a string in this case. A symbol will be prefixed with the
userstable 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.