ActiveRecord Deep — Query Interface and Advanced ORM Patterns

In this tutorial, you will learn about ActiveRecord Deep. We cover key concepts, practical examples, and best practices to help you master this topic.

Rails ActiveRecord provides a powerful query interface with eager loading, subqueries, calculations, pessimistic and optimistic locking, transactions, and SQL Injection prevention.

What You'll Learn

By the end of this tutorial, you'll write complex ActiveRecord queries, use eager loading with conditions, implement locking, compose subqueries, and optimize query performance.

Why ActiveRecord Matters

ActiveRecord is the ORM layer of Rails. Deep knowledge of its query interface leads to efficient database access and prevents N+1 queries and performance issues.

Real-World Use

A reporting system uses ActiveRecord calculations for revenue by month, eager loading with conditions for filtered relations, and pessimistic locking for inventory management.

ActiveRecord Path

flowchart LR
  A[Rails Models] --> B[ActiveRecord Deep]
  B --> C[Query Interface]
  B --> D[Eager Loading]
  B --> E[Locking]
  B --> F[Subqueries]
  B --> G{You Are Here}
  style G fill:#f90,color:#fff

Advanced Eager Loading

Eager load with conditions and select.

# Eager load with conditions
products = Product.includes(:reviews).where(reviews: { approved: true })
# Equivalent to:
products = Product.left_joins(:reviews).where(reviews: { approved: true }).includes(:reviews)

# Select specific columns in eager loading
users = User.includes(:profile).where(profiles: { id: nil })
# Using references
products = Product.includes(:category).where.not(categories: { id: nil }).references(:categories)

Subqueries

Use subqueries in ActiveRecord.

# WHERE clause subquery
Product.where("price > (SELECT AVG(price) FROM products)")

# Using from
recent_orders = Order.where("created_at > ?", 30.days.ago)
top_customers = Customer.where(id: recent_orders.select(:customer_id).group(:customer_id)
  .having("COUNT(*) >= 5").select(:customer_id))

# Subquery in select
Product.select(
  "products.*",
  "(SELECT COUNT(*) FROM reviews WHERE reviews.product_id = products.id) AS review_count"
)

Optimistic Locking

Prevent concurrent updates with a lock column.

# Add lock_version column to table
class AddLockVersionToProducts < ActiveRecord::Migration[7.1]
  def change
    add_column :products, :lock_version, :integer, default: 0
  end
end

# ActiveRecord automatically manages lock_version
product = Product.find(1)
# Another request loads the same product...
product.update!(price: 20.0) # Raises ActiveRecord::StaleObjectError if version changed

# Retry logic
begin
  product.update!(price: 20.0)
rescue ActiveRecord::StaleObjectError
  product.reload
  retry
end

Pessimistic Locking

Lock rows in the database during a Transaction.

# Row-level lock
Order.transaction do
  order = Order.lock("FOR UPDATE").find(1)
  inventory = Inventory.lock!("FOR UPDATE").find(order.product_id)
  if inventory.quantity >= order.quantity
    inventory.decrement!(:quantity, order.quantity)
    order.update!(status: :confirmed)
  else
    raise "Insufficient inventory"
  end
end

# Different lock types
Product.lock("FOR SHARE").find(1)      # Shared lock
Product.lock("FOR UPDATE NOWAIT").find(1) # No wait
Product.lock("FOR UPDATE SKIP LOCKED").find(1) # Skip locked rows

Common Mistakes

1. N+1 Queries with Includes

includes without where on the associated table still causes N+1. Use references for where conditions.

2. Forgetting Database-Level Constraints

Rails validations are not enough. Add database constraints for data integrity.

3. Over-Locking

Pessimistic locking everywhere hurts performance. Use optimistic locking by default.

4. Not Using Transactions for Multiple Writes

Multiple saves without a transaction can cause partial updates. Wrap in transaction.

5. SQL Injection via String Conditions

Using string conditions with interpolation is dangerous. Use parameterized queries.

Practice Questions

1. How do you prevent N+1 queries in ActiveRecord?

Use includes or eager_load to load associations upfront.

2. What is the difference between optimistic and pessimistic locking?

Optimistic checks version on update. Pessimistic locks rows in the database.

3. How do you write a subquery in ActiveRecord?

Use .where("column > (SELECT ...)") or .where(id: OtherModel.select(:column)).

4. What does lock("FOR UPDATE") do?

Locks the selected rows for update until the transaction ends.

5. Challenge: Write a query with subquery, eager loading, and locking.

Product.transaction do
  top_products = Product.where("sales_count > (SELECT AVG(sales_count) FROM products)")
    .includes(:category)
    .lock("FOR UPDATE")
    .limit(10)
  top_products.each { |p| p.update!(featured: true) }
end

FAQ

Mini Project: Inventory Management with Locking

Build inventory management with proper locking.

class InventoryManager
  def process_order(order)
    Order.transaction do
      product = Product.lock("FOR UPDATE").find(order.product_id)
      if product.quantity >= order.quantity
        product.decrement!(:quantity, order.quantity)
        order.update!(status: :confirmed)
      else
        order.update!(status: :failed, failure_reason: "Insufficient stock")
      end
    end
  end
end

What's Next

Rails Migrations Deep Rails Validations Deep Rails Associations Deep