32. Advanced Active Record Queries

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

1# Hash conditions (recommended)
2User.where(active: true)
3User.where(role: "admin", active: true)
4User.where(status: nil)
5User.where(role: ["admin", "moderator"])
6User.where(age: 18..65)
7User.where(created_at: 1.week.ago..)
8
9# String conditions with placeholders
10User.where("age > ?", 18)
11User.where("name LIKE ?", "%john%")
12User.where("age > ? AND role = ?", 18, "user")
13
14# Named placeholders
15User.where("created_at > :start AND created_at < :end", 
16           start: 1.week.ago, end: Time.current)
17
18# NOT conditions
19User.where.not(role: "admin")
20User.where.not(id: [1, 2, 3])
21
22# OR conditions
23User.where(role: "admin").or(User.where(role: "moderator"))
24
25# Missing associations
26Post.where.missing(:comments)
27
28# Associated records exist
29Post.where.associated(:author)

1# Ordering
2User.order(:name)
3User.order(name: :asc)
4User.order(name: :desc)
5User.order(:role, name: :desc)
6User.order(Arel.sql("LOWER(name)"))
7
8# Reverse order
9User.order(:name).reverse_order
10
11# Reorder (replace existing order)
12User.order(:name).reorder(:email)
13
14# Limiting
15User.limit(10)
16User.limit(10).offset(20)
17
18# Distinct
19User.select(:role).distinct

1# Select specific columns
2User.select(:id, :name, :email)
3User.select("name, email, UPPER(role) as role_upper")
4
5# Pluck (returns array of values, not objects)
6User.pluck(:email)
7# => ["john@example.com", "jane@example.com"]
8
9User.pluck(:id, :email)
10# => [[1, "john@example.com"], [2, "jane@example.com"]]
11
12# IDs only
13User.ids
14# => [1, 2, 3, 4, 5]
15
16# Pick (first matching value)
17User.where(role: "admin").pick(:email)
18# => "admin@example.com"

1# Group by
2Order.group(:status).count
3# => {"pending" => 10, "completed" => 50, "cancelled" => 5}
4
5Order.group(:status, :payment_method).count
6
7# Having (filter grouped results)
8Order.group(:user_id).having("COUNT(*) > ?", 5).count
9
10# Aggregate functions
11Product.count
12Product.sum(:price)
13Product.average(:price)
14Product.minimum(:price)
15Product.maximum(:price)
16
17# Aggregate with conditions
18Product.where(active: true).sum(:price)
19
20# Calculate
21Product.calculate(:sum, :price)

1# Inner join
2User.joins(:articles)
3User.joins(:articles, :comments)
4User.joins(articles: :comments)
5
6# With conditions on joined table
7User.joins(:articles).where(articles: { published: true })
8User.joins(:articles).where("articles.created_at > ?", 1.week.ago)
9
10# Left outer join
11User.left_joins(:articles)
12User.left_outer_joins(:articles)
13
14# Find users without articles
15User.left_joins(:articles).where(articles: { id: nil })

1# N+1 problem
2users = User.all
3users.each do |user|
4  puts user.articles.count  # Query for each user!
5end
6
7# Solution: includes
8users = User.includes(:articles)
9users.each do |user|
10  puts user.articles.size  # No additional queries
11end
12
13# Multiple associations
14User.includes(:articles, :comments)
15
16# Nested associations
17User.includes(articles: :comments)
18
19# Preload (always separate queries)
20User.preload(:articles)
21
22# Eager load (always LEFT OUTER JOIN)
23User.eager_load(:articles)
24
25# References (when filtering on includes)
26User.includes(:articles).where(articles: { published: true }).references(:articles)

1# Process records in batches (memory efficient)
2User.find_each do |user|
3  user.send_newsletter
4end
5
6# With batch size
7User.find_each(batch_size: 500) do |user|
8  user.process
9end
10
11# Get batches as arrays
12User.find_in_batches(batch_size: 1000) do |users|
13  users.each { |u| u.update_column(:processed, true) }
14end
15
16# Start from specific ID
17User.find_each(start: 1000) do |user|
18  # Process users with id >= 1000
19end
20
21# With order (Rails 7+)
22User.in_batches(order: :desc).each_record do |user|
23  user.archive
24end

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 with predicates
8User.any?
9User.none?
10User.one?
11User.many?
12
13# Empty check
14User.where(role: "admin").empty?

1# Complex statistics
2Order.group(:status)
3     .select("status, COUNT(*) as count, SUM(total) as revenue")
4     
5# Group by date
6Order.group("DATE(created_at)")
7     .select("DATE(created_at) as date, SUM(total) as daily_total")
8
9# Group by association
10Article.joins(:user)
11       .group("users.name")
12       .count

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 SQL
8User.select("*, CONCAT(first_name, ' ', last_name) as full_name")
9
10# Sanitize SQL
11query = ActiveRecord::Base.sanitize_sql(["SELECT * FROM users WHERE name = ?", name])

1# See query execution plan
2User.where(active: true).explain
3
4# Output shows how database will execute the query
5# Useful for optimizing slow queries