Rails N+1 query that cannot be fixed with includes() - using where() in instance method

n_plus_one_where.rb

# frozen_string_literal: true

require "bundler/inline"

gemfile(true) do
  source "https://rubygems.org"
  gem "rails", "~> 8.0"
  gem "sqlite3"
end

require "active_record"
require "action_controller/railtie"
require "logger"

# Set up ActiveRecord logging to see the queries
ActiveRecord::Base.logger = Logger.new($stdout)
ActiveRecord::Base.logger.level = Logger::DEBUG

# Connect to an in-memory SQLite database
ActiveRecord::Base.establish_connection(
  adapter: "sqlite3",
  database: ":memory:"
)

# Define the schema
ActiveRecord::Schema.define do
  create_table :posts, force: true do |t|
    t.string :title
  end

  create_table :comments, force: true do |t|
    t.references :post
    t.string :body
    t.integer :score, default: 0
  end
end

# Models
class Post < ActiveRecord::Base
  has_many :comments

  # This instance method uses `where`, which cannot be optimized by `includes`
  # because it creates a new query each time, bypassing any preloaded data
  def high_score_comments
    comments.where("score > ?", 5)
  end
end

class Comment < ActiveRecord::Base
  belongs_to :post
end

# Seed data
puts "\n" + "=" * 60
puts "SEEDING DATA"
puts "=" * 60 + "\n\n"

5.times do |i|
  post = Post.create!(title: "Post #{i + 1}")
  10.times do |j|
    Comment.create!(post: post, body: "Comment #{j + 1}", score: rand(1..10))
  end
end

puts "\n" + "=" * 60
puts "DEMONSTRATION 1: The N+1 Problem"
puts "=" * 60
puts "Fetching all posts and calling high_score_comments on each..."
puts "Watch for repeated SELECT statements on comments table:\n\n"

posts = Post.all
posts.each do |post|
  post.high_score_comments.to_a
end

puts "\n" + "=" * 60
puts "DEMONSTRATION 2: includes() Does NOT Fix This"
puts "=" * 60
puts "Now trying with includes(:comments)..."
puts "The N+1 still happens because where() bypasses preloaded data:\n\n"

posts_with_includes = Post.includes(:comments).all
posts_with_includes.each do |post|
  # Even though comments are preloaded, calling where() on them
  # creates a new query - the preloaded data is ignored
  post.high_score_comments.to_a
end

puts "\n" + "=" * 60
puts "CONCLUSION"
puts "=" * 60
puts <<~EXPLANATION

  The `includes` method preloads the association into memory, but when you
  call `where()` on that association, ActiveRecord issues a new database
  query instead of filtering the preloaded records in Ruby.

  This is a common source of N+1 queries that cannot be fixed with eager
  loading. Possible solutions include:

  1. Filter in Ruby: comments.select { |c| c.score > 5 }
  2. Use a separate association with a scope
  3. Preload into a hash and filter manually

EXPLANATION

Output

============================================================
DEMONSTRATION 1: The N+1 Problem
============================================================
Fetching all posts and calling high_score_comments on each...
Watch for repeated SELECT statements on comments table:

Post Load (0.0ms)  SELECT "posts".* FROM "posts"
Comment Load (0.0ms)  SELECT "comments".* FROM "comments" WHERE "comments"."post_id" = ? AND (score > ?)  [["post_id", 1], [nil, 5]]
Comment Load (0.0ms)  SELECT "comments".* FROM "comments" WHERE "comments"."post_id" = ? AND (score > ?)  [["post_id", 2], [nil, 5]]
Comment Load (0.0ms)  SELECT "comments".* FROM "comments" WHERE "comments"."post_id" = ? AND (score > ?)  [["post_id", 3], [nil, 5]]
Comment Load (0.0ms)  SELECT "comments".* FROM "comments" WHERE "comments"."post_id" = ? AND (score > ?)  [["post_id", 4], [nil, 5]]
Comment Load (0.0ms)  SELECT "comments".* FROM "comments" WHERE "comments"."post_id" = ? AND (score > ?)  [["post_id", 5], [nil, 5]]

============================================================
DEMONSTRATION 2: includes() Does NOT Fix This
============================================================
Now trying with includes(:comments)...
The N+1 still happens because where() bypasses preloaded data:

Post Load (0.0ms)  SELECT "posts".* FROM "posts"
Comment Load (0.1ms)  SELECT "comments".* FROM "comments" WHERE "comments"."post_id" IN (?, ?, ?, ?, ?)  [["post_id", 1], ["post_id", 2], ["post_id", 3], ["post_id", 4], ["post_id", 5]]
Comment Load (0.0ms)  SELECT "comments".* FROM "comments" WHERE "comments"."post_id" = ? AND (score > ?)  [["post_id", 1], [nil, 5]]
Comment Load (0.0ms)  SELECT "comments".* FROM "comments" WHERE "comments"."post_id" = ? AND (score > ?)  [["post_id", 2], [nil, 5]]
Comment Load (0.0ms)  SELECT "comments".* FROM "comments" WHERE "comments"."post_id" = ? AND (score > ?)  [["post_id", 3], [nil, 5]]
Comment Load (0.0ms)  SELECT "comments".* FROM "comments" WHERE "comments"."post_id" = ? AND (score > ?)  [["post_id", 4], [nil, 5]]
Comment Load (0.0ms)  SELECT "comments".* FROM "comments" WHERE "comments"."post_id" = ? AND (score > ?)  [["post_id", 5], [nil, 5]]

============================================================
CONCLUSION
============================================================

The includes method preloads the association into memory, but when you
call where() on that association, ActiveRecord issues a new database
query instead of filtering the preloaded records in Ruby.

This is a common source of N+1 queries that cannot be fixed with eager
loading. Possible solutions include:

1. Filter in Ruby: comments.select { |c| c.score > 5 }
2. Use a separate association with a scope
3. Preload into a hash and filter manually