🇨🇳中文介绍

ActiveRecord 查询模式

查询决策树

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

---

绝对不要这样做

绝对不要将 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

---

预加载快速参考

# 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')

---

GROUP BY（PostgreSQL 关键点）

规则：每个未聚合的 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

---

JSONB 快速参考

# 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 - 创建表、安全迁移、JSONB
• performance.md - 批量处理、计数器缓存、索引
• rails7-8-features.md - 复合主键、加密、多数据库
• advanced-patterns.md - 枚举、数据库视图、CTEs、STI
• postgresql-features.md - 全文搜索、JSONB、数组

每周安装量

–

来源

smithery.ai/ski…patterns

首次出现

–

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

# 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)