Eager loading data and magic attributes in ActiveRecord

I am not a fan of reading a Rails log and seeing a silly number of queries to the database particularly if they are for the same thing time and time again, sometimes exactly the same query but also sometimes a similar query that’s repeated for each object in a list. Yes, you can use includes to preload associations e.g Post.includes(:something) but what about something more complex like a ratings count.

The following is your pretty average polymorphic ratings table. Each Post has multiple ratings, and you just want to show the score.

class Post
  has_many :ratings, as: :rateable

  def score
    (ratings.pluck('AVG(score)')[0] || 0).to_i
  end
end

class Ratings
  belongs_to :rateable, polymorphic: true
end

So what happens when you display this as a list, something like this….

(0.3ms)  SELECT AVG(score) FROM `ratings` WHERE `ratings`.`rateable_id` = 1 AND `ratings`.`rateable_type` = 'Post'
  Rendered posts/_post.json.jbuilder (1.6ms)
   (0.3ms)  SELECT AVG(score) FROM `ratings` WHERE `ratings`.`rateable_id` = 2 AND `ratings`.`rateable_type` = 'Post'
  Rendered posts/_post.json.jbuilder (1.6ms)</pre></code>
   (0.3ms)  SELECT AVG(score) FROM `ratings` WHERE `ratings`.`rateable_id` = 3 AND `ratings`.`rateable_type` = 'Post'
  Rendered posts/_post.json.jbuilder (1.6ms)
   (0.3ms)  SELECT AVG(score) FROM `ratings` WHERE `ratings`.`rateable_id` = 4 AND `ratings`.`rateable_type` = 'Post'
  Rendered posts/_post.json.jbuilder (1.6ms)
   (0.3ms)  SELECT AVG(score) FROM `ratings` WHERE `ratings`.`rateable_id` = 5 AND `ratings`.`rateable_type` = 'Post'
  Rendered posts/_post.json.jbuilder (1.6ms)

Not particularly efficient. With every Post you increase the number of queries to the DB. Even if that is a millisecond 1,000 Post objects later it’s a second. And seconds feel clunky in a nice swanky interface. We could include the ratings objects for each Post.includes(:ratings) but that’s not very efficient either. We just need the count not multiple new Rating objects.

We could, however, add the scores count to the SELECT query. With ActiveRecord magic anything in the SELECT turns into an attribute of the object you’re fetching in this case Post. If you’re wondering what COALESCE means here, if there are no ratings the AVG of NULL will be NULL. COALESCE returns first NOT NULL result. COALESCE will return 0.

@posts = Post
    .group(:id)
    .left_joins(:ratings).distinct
    .select('cafes.*, COALESCE(AVG(ratings.score), 0) AS score')

Then we can alter the Post#score method to check it’s attributes first.

def score
    (attributes['score'] || ratings.pluck('AVG(score)')[0] || 0).to_i
  end

So we can king of eager load results. instead of fetching them in multiple hits to the database. Cool huh? And it’s shaved off 40ms on the DB time so while in this application it’s not going to kill us, why not be efficient from the outset and not fill the log with unnecessary queries.

Completed 200 OK in 382ms (Views: 337.1ms | ActiveRecord: 42.3ms)

vs..

Completed 200 OK in 282ms (Views: 262.6ms | ActiveRecord: 4.4ms)