ActiveRecord: แก้ปัญหาคิวรี N+1 ใน Ruby on Rails

# app/controllers/articles_controller.rb
# Example code generating an N+1 problem
class ArticlesController < ApplicationController
  def index
    # 1 query: SELECT * FROM articles
    @articles = Article.all
  end
end

<!-- app/views/articles/index.html.erb -->
<!-- This view generates N additional queries -->
<% @articles.each do |article| %>
  <div class="article">
    <h2><%= article.title %></h2>
    <!-- Each call generates: SELECT * FROM users WHERE id = ? -->
    <p>By <%= article.author.name %></p>
  </div>
<% end %>

-- Rails logs showing the N+1 problem
-- 1 initial query
SELECT "articles".* FROM "articles"

-- N queries for authors (repeated for each article)
SELECT "users".* FROM "users" WHERE "users"."id" = 1 LIMIT 1
SELECT "users".* FROM "users" WHERE "users"."id" = 2 LIMIT 1
SELECT "users".* FROM "users" WHERE "users"."id" = 3 LIMIT 1
-- ... 97 more queries

# app/controllers/articles_controller.rb
# Solution with includes - preloading authors
class ArticlesController < ApplicationController
  def index
    # Preloads authors with articles
    # Generates only 2 queries instead of N+1
    @articles = Article.includes(:author).all
  end
end

-- Rails logs with includes (only 2 queries)
SELECT "articles".* FROM "articles"
SELECT "users".* FROM "users" WHERE "users"."id" IN (1, 2, 3, 4, 5, ...)

# app/controllers/articles_controller.rb
# Preloading nested associations
class ArticlesController < ApplicationController
  def index
    # Preloads author -> company and all comments
    @articles = Article.includes(author: :company, comments: :user)
  end
end

# app/models/article.rb
# preload always uses separate queries
class Article < ApplicationRecord
  scope :with_authors, -> { preload(:author) }
end

# Usage in controller
@articles = Article.with_authors.limit(20)

# SQL generated:
# SELECT "articles".* FROM "articles" LIMIT 20
# SELECT "users".* FROM "users" WHERE "users"."id" IN (1, 2, 3, ...)

# app/controllers/articles_controller.rb
# eager_load allows filtering on associations
class ArticlesController < ApplicationController
  def verified_authors
    # Filters articles by author status
    # Requires eager_load because WHERE references users
    @articles = Article.eager_load(:author)
                       .where(users: { verified: true })
                       .order("users.name ASC")
  end
end

# SQL generated (single query with JOIN):
# SELECT "articles"."id", "articles"."title", ...
# FROM "articles"
# LEFT OUTER JOIN "users" ON "users"."id" = "articles"."author_id"
# WHERE "users"."verified" = TRUE
# ORDER BY "users"."name" ASC

# app/controllers/articles_controller.rb
# includes adapts automatically to context
class ArticlesController < ApplicationController
  def index
    # No condition on association: uses preload (2 queries)
    @articles = Article.includes(:author).all
  end

  def by_verified_authors
    # With condition on association: uses eager_load (JOIN)
    @articles = Article.includes(:author)
                       .where(users: { verified: true })
  end
end

# Gemfile
# Bullet detects N+1 in development
group :development do
  gem 'bullet'
end

# config/environments/development.rb
# Bullet configuration to detect N+1
Rails.application.configure do
  config.after_initialize do
    Bullet.enable = true
    # Display JavaScript alert in browser
    Bullet.alert = true
    # Add footer with details
    Bullet.bullet_logger = true
    # Display in Rails logs
    Bullet.rails_logger = true
    # Raise exception (useful in CI)
    Bullet.raise = false
  end
end

# Example Bullet alert in logs
USE eager loading detected
  Article => [:author]
  Add to your query: .includes([:author])
Call stack:
  /app/views/articles/index.html.erb:5

# Gemfile
# Prosopite as an alternative to Bullet
group :development, :test do
  gem 'prosopite'
end

# config/environments/development.rb
# Prosopite configuration
Rails.application.configure do
  config.after_initialize do
    Prosopite.rails_logger = true
    Prosopite.raise = Rails.env.test?
  end
end

# app/models/article.rb
# Enable strict loading by default on the model
class Article < ApplicationRecord
  # Any non-preloaded association access raises an exception
  self.strict_loading_by_default = true

  belongs_to :author
  has_many :comments
end

# app/controllers/articles_controller.rb
# Strict loading on a specific query
class ArticlesController < ApplicationController
  def index
    # Raises StrictLoadingViolationError if a non-included
    # association is accessed
    @articles = Article.strict_loading.includes(:author)
  end
end

# app/controllers/reports_controller.rb
# Optimization with select and pluck
class ReportsController < ApplicationController
  def titles_only
    # select returns Article objects with only id and title
    @articles = Article.select(:id, :title)
  end

  def title_array
    # pluck returns an Array of values, not AR objects
    # More performant when only values are needed
    @titles = Article.pluck(:title)
    # => ["First article", "Second article", ...]
  end
end

# app/models/comment.rb
# Counter cache configuration
class Comment < ApplicationRecord
  # Rails automatically maintains the counter in articles.comments_count
  belongs_to :article, counter_cache: true
end

# db/migrate/20260223_add_comments_count_to_articles.rb
# Migration to add counter cache
class AddCommentsCountToArticles < ActiveRecord::Migration[7.1]
  def change
    add_column :articles, :comments_count, :integer, default: 0, null: false

    # Initialize counters for existing data
    Article.find_each do |article|
      Article.reset_counters(article.id, :comments)
    end
  end
end

# app/views/articles/index.html.erb
# Using counter cache (no SQL query)
<% @articles.each do |article| %>
  <p><%= article.title %> - <%= article.comments_count %> comments</p>
<% end %>

# app/controllers/articles_controller.rb
# Pre-analyze view to identify required includes
class ArticlesController < ApplicationController
  def show
    # View accesses: author, author.company, comments, comments.user
    # All these must be preloaded
    @article = Article.includes(
      author: :company,
      comments: :user
    ).find(params[:id])
  end
end

# app/models/article.rb
# Reusable scopes for preloading
class Article < ApplicationRecord
  # Scope for list display
  scope :with_author, -> { includes(:author) }

  # Scope for detailed display
  scope :with_full_details, -> {
    includes(
      author: :company,
      comments: { user: :avatar_attachment },
      tags: []
    )
  }

  # Scope for admin with all relations
  scope :for_admin, -> {
    includes(:author, :comments, :tags, :category)
      .with_attached_cover_image
  }
end