The Agent Skills Directory
npx skills add https://smithery.ai/skills/Kaakati/activerecord-query-patternsWhat do I need?
│
├─ Find records by ID or attributes?
│ ├─ Single record: find(id), find_by(attrs)
│ └─ Multiple records: where(conditions)
│
├─ Access associated records?
│ ├─ Just filtering? → joins(:association)
│ └─ Loading data? → includes(:association)
│
├─ Aggregate data (count, sum, avg)?
│ └─ GROUP BY query
│ └─ REMEMBER: Every SELECT column must be in GROUP BY or aggregate
│
├─ Complex multi-step query?
│ └─ Query Object pattern (app/queries/)
│
├─ Hierarchical/recursive data?
│ └─ CTE (Common Table Expression)
│
└─ Full-text search?
└─ pg_search gem with tsvector indexes
绝对不要将 includes 与 group 一起使用:
# WRONG - PostgreSQL error
Task.includes(:carrier).group(:status).count
# RIGHT - Separate queries
status_counts = Task.group(:status).count
tasks = Task.where(status: status_counts.keys.first).includes(:carrier)
绝对不要在没有预加载的情况下进行迭代:
# WRONG - N+1 queries
tasks = Task.all
tasks.each { |t| puts t.carrier.name } # Query per task!
# RIGHT - Eager load
tasks = Task.includes(:carrier)
tasks.each { |t| puts t.carrier.name } # Single query
绝对不要将所有记录加载到内存中:
# WRONG - Memory explosion
Task.all.each { |task| process(task) }
# RIGHT - Batch processing
Task.find_each(batch_size: 1000) { |task| process(task) }
绝对不要使用 present? 来检查存在性:
# WRONG - Loads all records
Task.where(status: 'pending').present?
# RIGHT - Efficient existence check
Task.where(status: 'pending').exists?
绝对不要忘记在外键上建立索引:
# WRONG - No index
t.references :merchant, foreign_key: true, index: false
# RIGHT - Always index foreign keys
t.references :merchant, null: false, foreign_key: true # index: true is default
class Task < ApplicationRecord
# == Constants ==============================================================
STATUSES = %w[pending in_progress completed].freeze
# == Associations ===========================================================
belongs_to :account
belongs_to :merchant
belongs_to :carrier, optional: true
has_many :timelines, dependent: :destroy
# == Validations ============================================================
validates :status, presence: true, inclusion: { in: STATUSES }
validates :tracking_number, presence: true, uniqueness: { scope: :account_id }
# == Scopes =================================================================
scope :active, -> { where.not(status: 'completed') }
scope :for_carrier, ->(carrier) { where(carrier: carrier) }
# == Callbacks ==============================================================
before_validation :generate_tracking_number, on: :create
# == Class Methods ==========================================================
def self.search(query)
where("tracking_number ILIKE ?", "%#{query}%")
end
# == Instance Methods =======================================================
def complete!
update!(status: 'completed', completed_at: Time.current)
end
private
def generate_tracking_number
self.tracking_number ||= SecureRandom.hex(8).upcase
end
end
| 方法 | 查询类型 | 使用场景 |
|---|---|---|
includes | 智能(自动选择) | 默认选择 |
preload | 独立查询 | 无法在关联上过滤 |
eager_load | LEFT JOIN | 需要在关联上过滤 |
joins | INNER JOIN | 仅用于过滤,不加载数据 |
# Multiple associations
Task.includes(:carrier, :merchant, :recipient)
# Nested associations
Task.includes(merchant: :branches)
# Filter on association (requires references or use joins)
Task.joins(:carrier).where(carriers: { active: true })
# Simple scopes
scope :active, -> { where.not(status: 'completed') }
scope :recent, -> { order(created_at: :desc) }
# Parameterized scopes
scope :by_status, ->(status) { where(status: status) }
scope :created_after, ->(date) { where('created_at >= ?', date) }
# Conditional (always returns relation)
scope :by_status_if, ->(status) { where(status: status) if status.present? }
# Chainable
Task.active.recent.by_status('pending')
规则:每个未聚合的 SELECT 列都必须出现在 GROUP BY 子句中。
# CORRECT
Task.group(:status).count
Task.group(:status).sum(:amount)
Task.group(:status, :task_type).count
# CORRECT - Explicit select
Task.select(:status, 'COUNT(*) as count', 'AVG(amount) as avg')
.group(:status)
# Date grouping
Task.group("DATE(created_at)").count
class CreateTasks < ActiveRecord::Migration[7.1]
def change
create_table :tasks do |t|
t.references :account, null: false, foreign_key: true
t.string :tracking_number, null: false
t.string :status, null: false, default: 'pending'
t.decimal :amount, precision: 10, scale: 2
t.jsonb :metadata, default: {}
t.timestamps
t.index :tracking_number, unique: true
t.index :status
t.index [:account_id, :status]
t.index :metadata, using: :gin
end
end
end
# Concurrent index (large tables)
class AddIndex < ActiveRecord::Migration[7.1]
disable_ddl_transaction!
def change
add_index :tasks, :status, algorithm: :concurrently
end
end
Before writing any query:
[ ] Am I loading more columns than needed? → Use select/pluck
[ ] Am I iterating and accessing associations? → Use includes
[ ] Am I using GROUP BY? → Every SELECT column grouped or aggregated?
[ ] Am I using includes with GROUP BY? → DON'T! Separate queries
[ ] Will this query run on large table? → Check indexes exist
[ ] Am I loading all records? → Use find_each for batches
[ ] Am I checking existence? → Use exists? not present?
[ ] Do indexes exist for WHERE/ORDER columns?
class Task < ApplicationRecord
enum status: {
pending: 0,
in_progress: 1,
completed: 2
}, _prefix: true
# Generated methods:
# task.status_pending?
# task.status_completed!
# Task.status_pending (scope)
# Task.not_status_pending (scope)
end
# Migration
add_column :tasks, :metadata, :jsonb, default: {}
add_index :tasks, :metadata, using: :gin
# Queries
Task.where("metadata @> ?", { priority: 1 }.to_json) # Contains
Task.where("metadata ->> 'key' = ?", 'value') # Extract as text
Task.where("metadata ? 'key'") # Key exists
# Enable logging
ActiveRecord::Base.logger = Logger.new(STDOUT)
# Explain query plan
Task.where(status: 'pending').explain(:analyze)
# Use Bullet gem for N+1 detection
# Gemfile: gem 'bullet', group: :development
references/ 目录下的详细模式和示例:
associations.md - 关联类型、选项、多态关联query-patterns.md - 基础查询、预加载、子查询scopes-query-objects.md - 作用域模式、查询对象migrations.md - 创建表、安全迁移、JSONBperformance.md - 批量处理、计数器缓存、索引rails7-8-features.md - 复合主键、加密、多数据库advanced-patterns.md - 枚举、数据库视图、CTEs、STIpostgresql-features.md - 全文搜索、JSONB、数组每周安装量
–
来源
首次出现
–
What do I need?
│
├─ Find records by ID or attributes?
│ ├─ Single record: find(id), find_by(attrs)
│ └─ Multiple records: where(conditions)
│
├─ Access associated records?
│ ├─ Just filtering? → joins(:association)
│ └─ Loading data? → includes(:association)
│
├─ Aggregate data (count, sum, avg)?
│ └─ GROUP BY query
│ └─ REMEMBER: Every SELECT column must be in GROUP BY or aggregate
│
├─ Complex multi-step query?
│ └─ Query Object pattern (app/queries/)
│
├─ Hierarchical/recursive data?
│ └─ CTE (Common Table Expression)
│
└─ Full-text search?
└─ pg_search gem with tsvector indexes
NEVER use includes with group:
# WRONG - PostgreSQL error
Task.includes(:carrier).group(:status).count
# RIGHT - Separate queries
status_counts = Task.group(:status).count
tasks = Task.where(status: status_counts.keys.first).includes(:carrier)
广告位招租
在这里展示您的产品或服务
触达数万 AI 开发者,精准高效
NEVER iterate without eager loading:
# WRONG - N+1 queries
tasks = Task.all
tasks.each { |t| puts t.carrier.name } # Query per task!
# RIGHT - Eager load
tasks = Task.includes(:carrier)
tasks.each { |t| puts t.carrier.name } # Single query
NEVER load all records into memory:
# WRONG - Memory explosion
Task.all.each { |task| process(task) }
# RIGHT - Batch processing
Task.find_each(batch_size: 1000) { |task| process(task) }
NEVER use present? to check existence:
# WRONG - Loads all records
Task.where(status: 'pending').present?
# RIGHT - Efficient existence check
Task.where(status: 'pending').exists?
NEVER forget indexes on foreign keys:
# WRONG - No index
t.references :merchant, foreign_key: true, index: false
# RIGHT - Always index foreign keys
t.references :merchant, null: false, foreign_key: true # index: true is default
class Task < ApplicationRecord
# == Constants ==============================================================
STATUSES = %w[pending in_progress completed].freeze
# == Associations ===========================================================
belongs_to :account
belongs_to :merchant
belongs_to :carrier, optional: true
has_many :timelines, dependent: :destroy
# == Validations ============================================================
validates :status, presence: true, inclusion: { in: STATUSES }
validates :tracking_number, presence: true, uniqueness: { scope: :account_id }
# == Scopes =================================================================
scope :active, -> { where.not(status: 'completed') }
scope :for_carrier, ->(carrier) { where(carrier: carrier) }
# == Callbacks ==============================================================
before_validation :generate_tracking_number, on: :create
# == Class Methods ==========================================================
def self.search(query)
where("tracking_number ILIKE ?", "%#{query}%")
end
# == Instance Methods =======================================================
def complete!
update!(status: 'completed', completed_at: Time.current)
end
private
def generate_tracking_number
self.tracking_number ||= SecureRandom.hex(8).upcase
end
end
| Method | Query Type | Use Case |
|---|---|---|
includes | Smart (auto-selects) | Default choice |
preload | Separate queries | Can't filter on association |
eager_load | LEFT JOIN | Need to filter on association |
joins | INNER JOIN | Filtering only, no data loading |
# Multiple associations
Task.includes(:carrier, :merchant, :recipient)
# Nested associations
Task.includes(merchant: :branches)
# Filter on association (requires references or use joins)
Task.joins(:carrier).where(carriers: { active: true })
# Simple scopes
scope :active, -> { where.not(status: 'completed') }
scope :recent, -> { order(created_at: :desc) }
# Parameterized scopes
scope :by_status, ->(status) { where(status: status) }
scope :created_after, ->(date) { where('created_at >= ?', date) }
# Conditional (always returns relation)
scope :by_status_if, ->(status) { where(status: status) if status.present? }
# Chainable
Task.active.recent.by_status('pending')
Rule : Every non-aggregated SELECT column must appear in GROUP BY.
# CORRECT
Task.group(:status).count
Task.group(:status).sum(:amount)
Task.group(:status, :task_type).count
# CORRECT - Explicit select
Task.select(:status, 'COUNT(*) as count', 'AVG(amount) as avg')
.group(:status)
# Date grouping
Task.group("DATE(created_at)").count
class CreateTasks < ActiveRecord::Migration[7.1]
def change
create_table :tasks do |t|
t.references :account, null: false, foreign_key: true
t.string :tracking_number, null: false
t.string :status, null: false, default: 'pending'
t.decimal :amount, precision: 10, scale: 2
t.jsonb :metadata, default: {}
t.timestamps
t.index :tracking_number, unique: true
t.index :status
t.index [:account_id, :status]
t.index :metadata, using: :gin
end
end
end
# Concurrent index (large tables)
class AddIndex < ActiveRecord::Migration[7.1]
disable_ddl_transaction!
def change
add_index :tasks, :status, algorithm: :concurrently
end
end
Before writing any query:
[ ] Am I loading more columns than needed? → Use select/pluck
[ ] Am I iterating and accessing associations? → Use includes
[ ] Am I using GROUP BY? → Every SELECT column grouped or aggregated?
[ ] Am I using includes with GROUP BY? → DON'T! Separate queries
[ ] Will this query run on large table? → Check indexes exist
[ ] Am I loading all records? → Use find_each for batches
[ ] Am I checking existence? → Use exists? not present?
[ ] Do indexes exist for WHERE/ORDER columns?
class Task < ApplicationRecord
enum status: {
pending: 0,
in_progress: 1,
completed: 2
}, _prefix: true
# Generated methods:
# task.status_pending?
# task.status_completed!
# Task.status_pending (scope)
# Task.not_status_pending (scope)
end
# Migration
add_column :tasks, :metadata, :jsonb, default: {}
add_index :tasks, :metadata, using: :gin
# Queries
Task.where("metadata @> ?", { priority: 1 }.to_json) # Contains
Task.where("metadata ->> 'key' = ?", 'value') # Extract as text
Task.where("metadata ? 'key'") # Key exists
# Enable logging
ActiveRecord::Base.logger = Logger.new(STDOUT)
# Explain query plan
Task.where(status: 'pending').explain(:analyze)
# Use Bullet gem for N+1 detection
# Gemfile: gem 'bullet', group: :development
Detailed patterns and examples in references/:
associations.md - Association types, options, polymorphicquery-patterns.md - Basic queries, eager loading, subqueriesscopes-query-objects.md - Scope patterns, query objectsmigrations.md - Create table, safe migrations, JSONBperformance.md - Batch processing, counter caches, indexesrails7-8-features.md - Composite keys, encryption, multi-dbadvanced-patterns.md - Enums, database views, CTEs, STIpostgresql-features.md - Full-text search, JSONB, arraysWeekly Installs
–
Source
First Seen
–
GSAP 框架集成指南:Vue、Svelte 等框架中 GSAP 动画最佳实践
2,000 周安装