25. Querying the Database

1# Find by ID
2user = User.find(1)             # Raises ActiveRecord::RecordNotFound
3user = User.find([1, 2, 3])     # Returns array
4
5# Find by ID without exception
6user = User.find_by(id: 1)      # Returns nil if not found
7user = User.find_by!(id: 1)     # Raises exception
8
9# Find by any attribute
10user = User.find_by(email: "john@example.com")
11user = User.find_by(email: "john@example.com", active: true)
12
13# First and last
14user = User.first
15user = User.last
16user = User.first(5)            # Array of first 5
17
18# Take (no ordering)
19user = User.take                # Returns any record
20users = User.take(5)            # Any 5 records

1# Get all records
2users = User.all
3
4# Iterate without loading all into memory
5User.find_each do |user|
6  # Process user
7end
8
9User.find_each(batch_size: 500) do |user|
10  # Process in batches of 500
11end
12
13User.find_in_batches do |users|
14  # users is an array of up to 1000 records
15end

1# Hash conditions
2users = User.where(active: true)
3users = User.where(active: true, role: 'admin')
4users = User.where(status: nil)
5
6# String conditions (be careful with SQL injection!)
7users = User.where("age > 18")
8users = User.where("name LIKE ?", "%john%")
9users = User.where("age > ? AND role = ?", 18, 'user')
10
11# Named placeholders
12users = User.where("created_at > :date", date: 1.week.ago)
13
14# Array conditions
15users = User.where(role: ['admin', 'moderator'])
16users = User.where(id: [1, 2, 3, 4, 5])
17
18# Range conditions
19users = User.where(age: 18..65)
20users = User.where(created_at: 1.week.ago..Time.current)
21
22# Not conditions
23users = User.where.not(role: 'admin')
24users = User.where.not(id: [1, 2, 3])

1users = User.where(role: 'admin').or(User.where(role: 'moderator'))
2
3# Rails 7+ shorthand
4users = User.where(role: 'admin').or(User.where(role: 'moderator'))

1# Find records without associated records
2posts = Post.where.missing(:comments)
3
4# Find records with associated records
5posts = Post.where.associated(:comments)

1# Single column
2users = User.order(:name)
3users = User.order(name: :asc)
4users = User.order(name: :desc)
5users = User.order(:name, :email)
6
7# Multiple columns
8users = User.order(role: :asc, name: :asc)
9
10# String (be careful with SQL injection)
11users = User.order("name DESC")
12users = User.order(Arel.sql("LOWER(name)"))  # Safe SQL expression
13
14# With nulls
15users = User.order(Arel.sql("name NULLS LAST"))

1# Select specific columns
2users = User.select(:id, :name, :email)
3
4# Custom select
5users = User.select("name, email, COUNT(*) as count")
6
7# Distinct
8roles = User.select(:role).distinct
9emails = User.distinct.pluck(:email)

1# Limit results
2users = User.limit(10)
3
4# Offset (for pagination)
5users = User.limit(10).offset(20)
6
7# Shorthand for first/last with limit
8users = User.first(5)
9users = User.last(5)

1# Count users by role
2User.group(:role).count
3# => { "admin" => 5, "user" => 100, "moderator" => 10 }
4
5# Group with having
6User.group(:role).having("count(*) > ?", 5).count
7
8# Multiple groupings
9Order.group(:status, :payment_method).count

1# Count
2User.count
3User.where(active: true).count
4
5# Sum
6Order.sum(:total)
7Order.where(status: 'completed').sum(:total)
8
9# Average
10Product.average(:price)
11
12# Minimum and Maximum
13Product.minimum(:price)
14Product.maximum(:price)
15
16# Multiple aggregates
17Order.select("SUM(total) as total, AVG(total) as average").take

1# Join with another table
2posts = Post.joins(:user)
3posts = Post.joins(:user, :comments)
4
5# Nested joins
6posts = Post.joins(comments: :user)
7
8# With conditions
9posts = Post.joins(:user).where(users: { active: true })
10
11# String join (for complex joins)
12posts = Post.joins("INNER JOIN users ON users.id = posts.user_id")

1# Include records without matches
2users = User.left_joins(:posts)
3users = User.left_outer_joins(:posts)
4
5# Find users without posts
6users = User.left_joins(:posts).where(posts: { id: nil })

1# Prevent N+1 queries
2posts = Post.includes(:user)
3posts.each do |post|
4  puts post.user.name  # No additional query
5end
6
7# Multiple associations
8posts = Post.includes(:user, :comments)
9
10# Nested eager loading
11posts = Post.includes(comments: :user)
12
13# With conditions (uses JOIN)
14posts = Post.includes(:user).where(users: { active: true }).references(:users)

1# Get array of values (more efficient than map)
2emails = User.pluck(:email)
3# => ["john@example.com", "jane@example.com"]
4
5names_and_emails = User.pluck(:name, :email)
6# => [["John", "john@example.com"], ["Jane", "jane@example.com"]]
7
8# Get IDs
9ids = User.ids
10# => [1, 2, 3, 4, 5]
11
12# Pluck with conditions
13admin_emails = User.where(role: 'admin').pluck(:email)

1# Check if any records exist
2User.exists?
3User.exists?(1)
4User.exists?(email: "john@example.com")
5User.where(active: true).exists?
6
7# Check using any?
8User.where(role: 'admin').any?
9User.where(role: 'admin').none?
10User.where(role: 'admin').one?
11User.where(role: 'admin').many?

1# Queries are lazy - only executed when needed
2users = User.where(active: true)
3            .where(role: 'admin')
4            .order(:name)
5            .limit(10)
6
7# Executed when:
8users.to_a        # Convert to array
9users.each        # Iterate
10users.first       # Get first record
11users.count       # Get count
12users.pluck(:id)  # Get values

1# Combine queries from different models
2recent = Post.where("created_at > ?", 1.week.ago)
3published = Post.where(published: true)
4
5Post.merge(recent).merge(published)

1# Return no records (useful for conditionals)
2def search(params)
3  result = User.all
4  result = result.none if params[:query].blank?
5  result = result.where("name LIKE ?", "%#{params[:query]}%") if params[:query]
6  result
7end
8
9# Unscope
10User.where(active: true).unscope(:where)  # Removes all where conditions
11User.order(:name).unscope(:order)          # Removes order

1# Find by SQL
2users = User.find_by_sql("SELECT * FROM users WHERE role = 'admin'")
3
4# Execute raw SQL
5ActiveRecord::Base.connection.execute("UPDATE users SET active = true")
6
7# Select with raw SQL
8users = User.select("name, email, UPPER(role) as role_upper")