Active Record Aggregate Fields via Sub-Selecting Scopes

I was recently working on a piece of code from a legacy Rails application. An unusually large number of queries being run on a particular page let me know there was an N+1 query lurking.

The application was an online assessment platform dealing with assessments, questions, and responses. The question listing page was simply asking each question if it’s locked—which happens if it has any responses. So what’s the best way to query for lots of questions and their locked status?

Active Record has a built-in helper for managing count fields on records: MyModel.increment_counter(:thing_count, record_id). Now our model has a count that is updated atomically. However, we still need to track all the places where things get added, removed, or cleared out entirely. That becomes a harder task in a large legacy app, and it would be quite a refactor to properly load one query.

What if we used Active Record to build the query we want for the count of each child record, then used a named scope to nicely place that as a sub-select? We’d end up with the count in our query results, available via attributes on our model.

We can now rewrite our locked? method to see if our preloaded count aggregate is available, saving ourselves the N+1.

class Question
  scope :with_response_count, -> {
    subselect = "SELECT count(responses.id) FROM `questions` q
    left join assessment_template_questions atq on atq.question_id = q.id
    left join responses on responses.assessment_template_question_id = atq.id 
    WHERE `questions`.`archived` = 0 and q.id = questions.id group by q.id"

    select("questions.*, (#{subselect}) as response_count")
  }

  def locked?
    if attributes.key?('response_count')
      attributes['response_count'] > 0
    else
      Response.joins(:question).where('questions.id' => 1).exists?
    end
  end

end

I found this approach to be an interesting compromise in our large legacy codebase. Remember to make appropriate use of increment_counters, but add “subselected scopes” to the list of options for eager-loading Active Record models.