Refactored API using custom SQL query

api_controller.rb

# Original method: renders take up to 300ms, a stacktonne of N+1 queries
# GET /api/users_with_qualifications/ALS.json
def users_with_qualifications
  last_modified = User.with_qualifications(params[:qualification]).select("MAX(users.updated_at) as max").first.max
  render(:json => []) and return if last_modified.nil?

  if stale?(:last_modified => Time.parse(last_modified), :etag => params[:qualification])
    # TODO: find a way to have with_highest_qualifaction(...)
    qual = User::QUAL_LEVELS[params[:qualification]]
    users = User.with_qualifications(params[:qualification]).select("id, first_name, last_name, qualifications_mask").order('last_name ASC').all.reject do |r|
      max = 0
      r.qualifications.each do |q|
        index = User::QUAL_LEVELS[q]
        max = index if index > max
      end
      max > qual
    end

    render :json => users.as_json
  end
end

# New method: renders in ~12ms, 1 query
# GET /api/users_with_qualifications/ALS.json
def users_with_qualifications
  @users = User.with_qualifications(params[:qualification]).
      joins("LEFT OUTER JOIN (requests INNER JOIN shifts ON requests.shift_id = shifts.id AND shifts.starts_at BETWEEN (now() - INTERVAL '28 days') AND now()) ON requests.user_id = users.id").
      select("users.id, users.first_name, users.last_name, users.first_name||' '||users.last_name AS name, users.qualifications_mask, COUNT(requests.id) AS recent_shifts").
      group("users.id, users.first_name, users.last_name").order("users.last_name ASC")

  render :json => @users
end