# BAD: N+1 query problem
@users = User.all
@users.each do |user|
puts user.posts.count # Fires query for each user!
end
# GOOD: Eager loading with includes
@users = User.includes(:posts).all
@users.each do |user|
puts user.posts.count # No additional queries
end
# Multiple associations
@users = User.includes(:posts, :comments, profile: :avatar)
# Conditional eager loading
@users = User.includes(:posts).where(posts: { published: true })
# Using joins for filtering (doesn't load association)
@users = User.joins(:posts).where(posts: { status: 'published' }).distinct
# Preload vs Eager Load
User.preload(:posts) # Always 2 queries
User.eager_load(:posts) # Always LEFT OUTER JOIN
User.includes(:posts) # Smart choice based on conditions
# Select specific columns
User.select(:id, :name, :email) # Only fetch needed columns
# Pluck for single/multiple columns (returns array, not AR objects)
User.pluck(:email) # => ["alice@example.com", ...]
User.pluck(:id, :name) # => [[1, "Alice"], [2, "Bob"]]
# exists? vs present?/any?
User.where(status: 'active').exists? # Fast COUNT query
User.where(status: 'active').any? # Loads records into memory
class Post < ApplicationRecord
belongs_to :user
has_many :comments
# Scopes for reusable queries
scope :published, -> { where(status: 'published') }
scope :recent, -> { where('created_at > ?', 1.week.ago) }
scope :popular, -> { where('views_count > ?', 1000) }
scope :by_author, ->(user_id) { where(user_id: user_id) }
# Combining scopes
scope :trending, -> { published.recent.popular }
# Counter cache
belongs_to :user, counter_cache: true # Adds posts_count to users
# Custom SQL with sanitization
def self.search(term)
where('title ILIKE :term OR content ILIKE :term', term: "%#{sanitize_sql_like(term)}%")
end
# Subqueries
def self.with_recent_comments
where(id: Comment.select(:post_id).where('created_at > ?', 1.day.ago))
end
# Aggregations
def self.statistics
group(:status).count
# => { "published" => 150, "draft" => 42 }
end
def self.avg_views_by_category
joins(:category).group('categories.name').average(:views_count)
end
end
# Batch processing for large datasets
User.find_each(batch_size: 1000) do |user|
user.update_statistics
end
# find_in_batches returns arrays
User.find_in_batches(batch_size: 500) do |users|
UserExportService.export(users)
end
# Merge scopes
class User < ApplicationRecord
scope :active, -> { where(status: 'active') }
end
Post.joins(:user).merge(User.active)
# Upsert (Rails 6+)
User.upsert(
{ email: 'alice@example.com', name: 'Alice' },
unique_by: :email
)
# Upsert multiple
User.upsert_all([
{ email: 'alice@example.com', name: 'Alice' },
{ email: 'bob@example.com', name: 'Bob' }
], unique_by: :email)
# Migration with indices
class CreatePosts < ActiveRecord::Migration[6.1]
def change
create_table :posts do |t|
t.references :user, null: false, foreign_key: true, index: true
t.string :title, null: false
t.text :content
t.string :status, null: false, default: 'draft'
t.integer :views_count, default: 0
t.timestamps
end
# Composite index for common queries
add_index :posts, [:user_id, :status]
add_index :posts, [:status, :created_at]
# Partial index (PostgreSQL)
add_index :posts, :created_at, where: "status = 'published'"
# Full-text search index (PostgreSQL)
execute "CREATE INDEX posts_content_search_idx ON posts USING gin(to_tsvector('english', content))"
end
end
# Query analysis
Post.where(user_id: 1, status: 'published').explain
# Shows query plan and index usage
# Benchmark queries
require 'benchmark'
Benchmark.bm do |x|
x.report("without index:") { Post.where(title: 'Test').load }
x.report("with index:") { Post.where(title: 'Test').load }
end
ActiveRecord provides powerful query interface, but naive usage causes N+1 queries. includes eager loads associations in 2-3 queries. joins performs SQL JOINs for filtering. preload always uses separate queries; eager_load forces LEFT OUTER JOIN. I use select to limit columns, reducing memory. find_each and find_in_batches process large datasets efficiently. Scopes chain for composable queries. merge combines scopes. Counter caches avoid COUNT queries. Database indices speed lookups—I add indices on foreign keys and frequently queried columns. explain reveals query plans. Bullet gem detects N+1 in development. Proper query optimization dramatically improves performance, especially at scale.
