Activerecord Query Patterns

Association Patterns

Basic Associations

# One-to-Many
class Account < ApplicationRecord
  has_many :users, dependent: :destroy
  has_many :tasks, dependent: :destroy
end

class User < ApplicationRecord
  belongs_to :account
end

# Many-to-Many (with join table)
class Task < ApplicationRecord
  has_many :task_tags, dependent: :destroy
  has_many :tags, through: :task_tags
end

class Tag < ApplicationRecord
  has_many :task_tags, dependent: :destroy
  has_many :tasks, through: :task_tags
end

class TaskTag < ApplicationRecord
  belongs_to :task
  belongs_to :tag
end

# Polymorphic
class Comment < ApplicationRecord
  belongs_to :commentable, polymorphic: true
end

class Task < ApplicationRecord
  has_many :comments, as: :commentable
end

class Invoice < ApplicationRecord
  has_many :comments, as: :commentable
end

Association Options

class Task < ApplicationRecord
  # Foreign key specification
  belongs_to :creator, class_name: 'User', foreign_key: 'created_by_id'
  
  # Optional association
  belongs_to :carrier, optional: true
  
  # Counter cache
  belongs_to :merchant, counter_cache: true
  
  # Dependent options
  has_many :photos, dependent: :destroy      # Delete associated records
  has_many :logs, dependent: :nullify        # Set foreign key to NULL
  has_many :exports, dependent: :restrict_with_error  # Prevent deletion
  
  # Scoped association
  has_many :active_timelines, -> { where(active: true) }, class_name: 'Timeline'
  
  # Touch parent on update
  belongs_to :bundle, touch: true
end

Query Patterns

Basic Queries

# Find
Task.find(1)                    # Raises RecordNotFound
Task.find_by(id: 1)             # Returns nil if not found
Task.find_by!(id: 1)            # Raises RecordNotFound

# Where
Task.where(status: 'pending')
Task.where(status: %w[pending in_progress])  # IN query
Task.where.not(status: 'completed')
Task.where(created_at: 1.week.ago..)         # Range (>= date)
Task.where(created_at: ..1.week.ago)         # Range (<= date)
Task.where(created_at: 1.month.ago..1.week.ago)  # Between

# Order
Task.order(created_at: :desc)
Task.order(:status, created_at: :desc)

# Limit & Offset
Task.limit(10).offset(20)

# Distinct
Task.distinct.pluck(:status)

Avoiding N+1 Queries

# WRONG - N+1 query
tasks = Task.all
tasks.each { |t| puts t.carrier.name }  # Query per task!

# CORRECT - Eager loading
tasks = Task.includes(:carrier)
tasks.each { |t| puts t.carrier.name }  # Single query

# Multiple associations
Task.includes(:carrier, :merchant, :recipient)

# Nested associations
Task.includes(merchant: :branches)

# With conditions on association (use joins or references)
Task.includes(:carrier).where(carriers: { active: true }).references(:carriers)
# OR
Task.joins(:carrier).where(carriers: { active: true })

Choosing Loading Strategy

# includes - Smart loading (preload or eager_load based on usage)
Task.includes(:carrier)

# preload - Separate queries (can't filter on association)
Task.preload(:carrier)
# SELECT * FROM tasks
# SELECT * FROM carriers WHERE id IN (...)

# eager_load - Single LEFT JOIN query
Task.eager_load(:carrier)
# SELECT tasks.*, carriers.* FROM tasks LEFT JOIN carriers...

# joins - INNER JOIN (no loading, just filtering)
Task.joins(:carrier).where(carriers: { active: true })

GROUP BY Queries (Critical for PostgreSQL)

Rule: Every non-aggregated column in SELECT must appear in GROUP BY.

# CORRECT - Only grouped columns and aggregates
Task.group(:status).count
# => { "pending" => 10, "completed" => 25 }

Task.group(:status).sum(:amount)
# => { "pending" => 1000, "completed" => 5000 }

# CORRECT - Multiple GROUP BY columns
Task
  .group(:status, :task_type)
  .count
# => { ["pending", "express"] => 5, ["completed", "standard"] => 10 }

# CORRECT - Explicit select with aggregates
Task
  .select(:status, 'COUNT(*) as task_count', 'AVG(amount) as avg_amount')
  .group(:status)

# CORRECT - Date grouping
Task
  .group("DATE(created_at)")
  .count

# WRONG - includes with group
Task.includes(:carrier).group(:status).count  # ERROR!

# CORRECT - Separate queries if you need associated data
status_counts = Task.group(:status).count
tasks_by_status = status_counts.keys.each_with_object({}) do |status, hash|
  hash[status] = Task.where(status: status).includes(:carrier).limit(5)
end

Subqueries

# Subquery in WHERE
active_carrier_ids = Carrier.where(active: true).select(:id)
Task.where(carrier_id: active_carrier_ids)
# SELECT * FROM tasks WHERE carrier_id IN (SELECT id FROM carriers WHERE active = true)

# Subquery with join
Task.where(carrier_id: Carrier.active.select(:id))
    .where(merchant_id: Merchant.premium.select(:id))

Raw SQL (When Needed)

# Safe with sanitization
Task.where("created_at > ?", 1.week.ago)
Task.where("description ILIKE ?", "%#{query}%")

# Named bindings
Task.where("status = :status AND amount > :min", status: 'pending', min: 100)

# Select with raw SQL
Task.select("*, amount * 0.1 as commission")

# Find by SQL
Task.find_by_sql(["SELECT * FROM tasks WHERE status = ?", 'pending'])

Scope Patterns

Simple Scopes

class Task < ApplicationRecord
  scope :active, -> { where.not(status: %w[completed cancelled]) }
  scope :completed, -> { where(status: 'completed') }
  scope :recent, -> { order(created_at: :desc) }
  scope :today, -> { where(created_at: Time.current.all_day) }
end

Parameterized Scopes

class Task < ApplicationRecord
  scope :by_status, ->(status) { where(status: status) }
  scope :created_after, ->(date) { where('created_at >= ?', date) }
  scope :for_carrier, ->(carrier_id) { where(carrier_id: carrier_id) }
  
  # With default
  scope :recent, ->(limit = 10) { order(created_at: :desc).limit(limit) }
  
  # Conditional scope
  scope :by_status_if_present, ->(status) { where(status: status) if status.present? }
end

Chainable Scopes

# All scopes are chainable
Task.active.recent.by_status('pending').for_carrier(123)

# Combine with where
Task.active.where(merchant_id: 456)

Query Objects

# app/queries/tasks/pending_delivery_query.rb
module Tasks
  class PendingDeliveryQuery
    def initialize(relation = Task.all)
      @relation = relation
    end

    def call(zone_id: nil, since: 24.hours.ago)
      result = @relation
        .where(status: 'pending')
        .where('created_at >= ?', since)
        .includes(:carrier, :recipient)
      
      result = result.where(zone_id: zone_id) if zone_id.present?
      result.order(created_at: :asc)
    end
  end
end

# Usage
Tasks::PendingDeliveryQuery.new.call(zone_id: 123)
Tasks::PendingDeliveryQuery.new(account.tasks).call(since: 1.hour.ago)

Migration Patterns

Create Table

class CreateTasks < ActiveRecord::Migration[7.1]
  def change
    create_table :tasks do |t|
      t.references :account, null: false, foreign_key: true
      t.references :merchant, null: false, foreign_key: true
      t.references :carrier, foreign_key: true  # nullable
      
      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.datetime :completed_at
      t.timestamps
      
      t.index :tracking_number, unique: true
      t.index :status
      t.index [:account_id, :status]
      t.index [:merchant_id, :created_at]
      t.index :metadata, using: :gin  # For JSONB queries
    end
  end
end

Safe Migrations

# Add column with default (safe in PostgreSQL 11+)
class AddPriorityToTasks < ActiveRecord::Migration[7.1]
  def change
    add_column :tasks, :priority, :integer, default: 0, null: false
  end
end

# Add index concurrently (for large tables)
class AddIndexToTasksStatus < ActiveRecord::Migration[7.1]
  disable_ddl_transaction!

  def change
    add_index :tasks, :status, algorithm: :concurrently
  end
end

# Remove column safely
class RemoveOldColumnFromTasks < ActiveRecord::Migration[7.1]
  def change
    safety_assured { remove_column :tasks, :old_column, :string }
  end
end

JSONB Columns

# Migration
add_column :tasks, :metadata, :jsonb, default: {}
add_index :tasks, :metadata, using: :gin

# Model
class Task < ApplicationRecord
  # Using jsonb_accessor gem
  jsonb_accessor :metadata,
    priority: :integer,
    tags: [:string, array: true],
    notes: :string
end

# Queries
Task.where("metadata @> ?", { priority: 1 }.to_json)
Task.where("metadata->>'priority' = ?", '1')
Task.where("metadata ? 'special_flag'")

Performance Optimization

Batch Processing

# WRONG - Loads all records into memory
Task.all.each { |task| process(task) }

# CORRECT - Batches of 1000
Task.find_each(batch_size: 1000) { |task| process(task) }

# With specific order
Task.order(:id).find_each { |task| process(task) }

# In batches (for batch operations)
Task.in_batches(of: 1000) do |batch|
  batch.update_all(processed: true)
end

Select Only Needed Columns

# WRONG - Loads all columns
users = User.all
users.each { |u| puts u.email }

# CORRECT - Only needed columns
users = User.select(:id, :email)
users.each { |u| puts u.email }

# With pluck (returns arrays, not AR objects)
emails = User.pluck(:email)

Counter Caches

# Migration
add_column :merchants, :tasks_count, :integer, default: 0

# Model
class Task < ApplicationRecord
  belongs_to :merchant, counter_cache: true
end

# Now merchant.tasks_count doesn't query
merchant.tasks_count  # Uses cached count

Exists? vs Any? vs Present?

# EFFICIENT - Stops at first match
Task.where(status: 'pending').exists?
# SELECT 1 FROM tasks WHERE status = 'pending' LIMIT 1

# LESS EFFICIENT - Loads records
Task.where(status: 'pending').any?
# May load records depending on implementation

# INEFFICIENT - Loads all records
Task.where(status: 'pending').present?
# SELECT * FROM tasks WHERE status = 'pending'

Explain & Analyze

# In Rails console
Task.where(status: 'pending').explain
Task.where(status: 'pending').explain(:analyze)

# Check for sequential scans on large tables
# Look for "Seq Scan" - may need index

Debugging Queries

# In Rails console, enable query logging
ActiveRecord::Base.logger = Logger.new(STDOUT)

# Or in development.rb
config.active_record.verbose_query_logs = true

# Using bullet gem for N+1 detection
# Gemfile: gem 'bullet', group: :development

Rails 7.x/8.x Modern Features

Composite Primary Keys (Rails 7.1+)

# Migration
class CreateBookOrders < ActiveRecord::Migration[7.1]
  def change
    create_table :book_orders, primary_key: [:shop_id, :id] do |t|
      t.integer :shop_id
      t.integer :id
      t.string :status
      t.timestamps
    end
  end
end

# Model
class BookOrder < ApplicationRecord
  self.primary_key = [:shop_id, :id]

  belongs_to :shop
  has_many :line_items, foreign_key: [:shop_id, :order_id]
end

# Usage
order = BookOrder.find([shop_id: 1, id: 100])
order.id # => { shop_id: 1, id: 100 }

ActiveRecord::Encryption (Rails 7+)

For encrypting sensitive data at rest:

# config/credentials.yml.enc
active_record_encryption:
  primary_key: <%= ENV['AR_ENCRYPTION_PRIMARY_KEY'] %>
  deterministic_key: <%= ENV['AR_ENCRYPTION_DETERMINISTIC_KEY'] %>
  key_derivation_salt: <%= ENV['AR_ENCRYPTION_KEY_DERIVATION_SALT'] %>

# Model
class User < ApplicationRecord
  encrypts :email               # Non-deterministic (can't query)
  encrypts :ssn, deterministic: true  # Deterministic (can query equality)
  encrypts :credit_card, ignore_case: true
end

# Queries with deterministic encryption
User.where(ssn: '123-45-6789')  # Works with deterministic: true
User.where(email: '[email protected]')  # Doesn't work without deterministic

# Unencrypted reads (for migration)
class User < ApplicationRecord
  encrypts :email, ignore_case: true, previous: { ignore_case: false }
end

Multi-Database Configuration (Rails 6.1+)

# config/database.yml