Active Record 查詢介面

class Author < ApplicationRecord
  has_many :books, -> { order(year_published: :desc) }
end

class Book < ApplicationRecord
  belongs_to :supplier
  belongs_to :author
  has_many :reviews
  has_and_belongs_to_many :orders, join_table: 'books_orders'

  scope :in_print, -> { where(out_of_print: false) }
  scope :out_of_print, -> { where(out_of_print: true) }
  scope :old, -> { where(year_published: ...50.years.ago.year) }
  scope :out_of_print_and_expensive, -> { out_of_print.where('price > 500') }
  scope :costs_more_than, ->(amount) { where('price > ?', amount) }
end

class Customer < ApplicationRecord
  has_many :orders
  has_many :reviews
end

class Order < ApplicationRecord
  belongs_to :customer
  has_and_belongs_to_many :books, join_table: 'books_orders'

  enum :status, [:shipped, :being_packed, :complete, :cancelled]

  scope :created_before, ->(time) { where(created_at: ...time) }
end

class Review < ApplicationRecord
  belongs_to :customer
  belongs_to :book

  enum :state, [:not_reviewed, :published, :hidden]
end

class Supplier < ApplicationRecord
  has_many :books
  has_many :authors, through: :books
end

# 查找主鍵（id）為 10 的客戶。
irb> customer = Customer.find(10)
=> #<Customer id: 10, first_name: "Ryan">

SELECT * FROM customers WHERE (customers.id = 10) LIMIT 1

# 查找主鍵為 1 和 10 的客戶。
irb> customers = Customer.find([1, 10]) # OR Customer.find(1, 10)
=> [#<Customer id: 1, first_name: "Lifo">, #<Customer id: 10, first_name: "Ryan">]

SELECT * FROM customers WHERE (customers.id IN (1,10))

irb> customer = Customer.take
=> #<Customer id: 1, first_name: "Lifo">

SELECT * FROM customers LIMIT 1

irb> customers = Customer.take(2)
=> [#<Customer id: 1, first_name: "Lifo">, #<Customer id: 220, first_name: "Sara">]

SELECT * FROM customers LIMIT 2

irb> customer = Customer.first
=> #<Customer id: 1, first_name: "Lifo">

SELECT * FROM customers ORDER BY customers.id ASC LIMIT 1

SELECT * FROM customers ORDER BY customers.id ASC LIMIT 3

irb> customer = Customer.order(:first_name).first
=> #<Customer id: 2, first_name: "Fifo">

SELECT * FROM customers ORDER BY customers.first_name ASC LIMIT 1

irb> customer = Customer.last
=> #<Customer id: 221, first_name: "Russel">

SELECT * FROM customers ORDER BY customers.id DESC LIMIT 1

irb> customers = Customer.last(3)
=> [#<Customer id: 219, first_name: "James">, #<Customer id: 220, first_name: "Sara">, #<Customer id: 221, first_name: "Russel">]

SELECT * FROM customers ORDER BY customers.id DESC LIMIT 3

irb> customer = Customer.order(:first_name).last
=> #<Customer id: 220, first_name: "Sara">

SELECT * FROM customers ORDER BY customers.first_name DESC LIMIT 1

irb> Customer.find_by first_name: 'Lifo'
=> #<Customer id: 1, first_name: "Lifo">

irb> Customer.find_by first_name: 'Jon'
=> nil

Customer.where(first_name: 'Lifo').take

SELECT * FROM customers WHERE (customers.first_name = 'Lifo') LIMIT 1

irb> Customer.find_by! first_name: 'does not exist'
ActiveRecord::RecordNotFound

Customer.where(first_name: 'does not exist').take!

# 如果表很大，這可能會消耗太多內存。
Customer.all.each do |customer|
  NewsMailer.weekly(customer).deliver_now
end

Customer.find_each do |customer|
  NewsMailer.weekly(customer).deliver_now
end

Customer.where(weekly_subscriber: true).find_each do |customer|
  NewsMailer.weekly(customer).deliver_now
end

Customer.find_each(batch_size: 5000) do |customer|
  NewsMailer.weekly(customer).deliver_now
end

Customer.find_each(start: 2000) do |customer|
  NewsMailer.weekly(customer).deliver_now
end

Customer.find_each(order: :desc) do |customer|
  NewsMailer.weekly(customer).deliver_now
end

# 每次給 add_customers 一個包含 1000 個客戶的數組。
Customer.find_in_batches do |customers|
  export.add_customers(customers)
end

# 每次給 add_customers 一個包含 1000 個最近活躍客戶的數組。
Customer.recently_active.find_in_batches do |customers|
  export.add_customers(customers)
end

Customer.find_in_batches(batch_size: 2500) do |customers|
  export.add_customers(customers)
end

Customer.find_in_batches(batch_size: 2500, start: 5000) do |customers|
  export.add_customers(customers)
end

Customer.find_in_batches(finish: 7000) do |customers|
  export.add_customers(customers)
end

Book.where("title = ?", params[:title])

Book.where("title = ? AND out_of_print = ?", params[:title], false)

Book.where("title = ?", params[:title])

Book.where("title = #{params[:title]}")

Book.where("created_at >= :start_date AND created_at <= :end_date",
  { start_date: params[:start_date], end_date: params[:end_date] })

Book.where("title LIKE ?", params[:title] + "%")

Book.where("title LIKE ?",
  Book.sanitize_sql_like(params[:title]) + "%")

Book.where(out_of_print: true)

SELECT * FROM books WHERE (books.out_of_print = 1)

Book.where('out_of_print' => true)

Book.where(created_at: (Time.now.midnight - 1.day)..Time.now.midnight)

SELECT * FROM books WHERE (books.created_at BETWEEN '2008-12-21 00:00:00' AND '2008-12-22 00:00:00')

Book.where(created_at: (Time.now.midnight - 1.day)..)

SELECT * FROM books WHERE books.created_at >= '2008-12-21 00:00:00'

Customer.where(orders_count: [1, 3, 5])

SELECT * FROM customers WHERE (customers.orders_count IN (1,3,5))

Customer.where.not(orders_count: [1, 3, 5])

SELECT * FROM customers WHERE (customers.orders_count NOT IN (1,3,5))

Customer.create!(nullable_country: nil)
Customer.where.not(nullable_country: "UK")
=> []
# 但是
Customer.create!(nullable_country: "UK")
Customer.where.not(nullable_country: nil)
=> [#<Customer id: 2, nullable_country: "UK">]

Customer.where(last_name: 'Smith').or(Customer.where(orders_count: [1, 3, 5]))

SELECT * FROM customers WHERE (customers.last_name = 'Smith' OR customers.orders_count IN (1,3,5))

Customer.where(last_name: 'Smith').where(orders_count: [1, 3, 5])

SELECT * FROM customers WHERE customers.last_name = 'Smith' AND customers.orders_count IN (1,3,5)

Customer.where(id: [1, 2]).and(Customer.where(id: [2, 3]))

SELECT * FROM customers WHERE (customers.id IN (1, 2) AND customers.id IN (2, 3))

Book.order(:created_at)
# 或者
Book.order("created_at")

Book.order(created_at: :desc)
# 或者
Book.order(created_at: :asc)
# 或者
Book.order("created_at DESC")
# 或者
Book.order("created_at ASC")

Book.order(title: :asc, created_at: :desc)
# 或者
Book.order(:title, created_at: :desc)
# 或者
Book.order("title ASC, created_at DESC")
# 或者
Book.order("title ASC", "created_at DESC")

irb> Book.order("title ASC").order("created_at DESC")
SELECT * FROM books ORDER BY title ASC, created_at DESC

Book.select(:isbn, :out_of_print)
# 或者
Book.select("isbn, out_of_print")

SELECT isbn, out_of_print FROM books

ActiveModel::MissingAttributeError: missing attribute '<attribute>' for Book

Customer.select(:last_name).distinct

SELECT DISTINCT last_name FROM customers

# 返回唯一的last_names
query = Customer.select(:last_name).distinct

# 返回所有的last_names，即使有重複的
query.distinct(false)

Customer.limit(5)

SELECT * FROM customers LIMIT 5

Customer.limit(5).offset(30)

SELECT * FROM customers LIMIT 5 OFFSET 30

Order.select("created_at").group("created_at")

SELECT created_at
FROM orders
GROUP BY created_at

irb> Order.group(:status).count
=> {"being_packed"=>7, "shipped"=>12}

SELECT COUNT (*) AS count_all, status AS status
FROM orders
GROUP BY status

Order.select("created_at, sum(total) as total_price").
  group("created_at").having("sum(total) > ?", 200)

SELECT created_at as ordered_date, sum(total) as total_price
FROM orders
GROUP BY created_at
HAVING sum(total) > 200

big_orders = Order.select("created_at, sum(total) as total_price")
                  .group("created_at")
                  .having("sum(total) > ?", 200)

big_orders[0].total_price
# 返回第一個 Order 物件的總價

Book.where('id > 100').limit(20).order('id desc').unscope(:order)

SELECT * FROM books WHERE id > 100 LIMIT 20

-- 沒有 `unscope` 的原始查詢
SELECT * FROM books WHERE id > 100 ORDER BY id desc LIMIT 20

Book.where(id: 10, out_of_print: false).unscope(where: :id)
# SELECT books.* FROM books WHERE out_of_print = 0

Book.order('id desc').merge(Book.unscope(:order))
# SELECT books.* FROM books

Book.where('id > 10').limit(20).order('id desc').only(:order, :where)

SELECT * FROM books WHERE id > 10 ORDER BY id DESC

-- 沒有 `only` 的原始查詢
SELECT * FROM books WHERE id > 10 ORDER BY id DESC LIMIT 20

Book.select(:title, :isbn).reselect(:created_at)

SELECT books.created_at FROM books

Book.select(:title, :isbn).select(:created_at)

SELECT books.title, books.isbn, books.created_at FROM books

class Author < ApplicationRecord
  has_many :books, -> { order(year_published: :desc) }
end

Author.find(10).books

SELECT * FROM authors WHERE id = 10 LIMIT 1
SELECT * FROM books WHERE author_id = 10 ORDER BY year_published DESC

Author.find(10).books.reorder('year_published ASC')

SELECT * FROM authors WHERE id = 10 LIMIT 1
SELECT * FROM books WHERE author_id = 10 ORDER BY year_published ASC

Book.where("author_id > 10").order(:year_published).reverse_order

SELECT * FROM books WHERE author_id > 10 ORDER BY year_published DESC

Book.where("author_id > 10").reverse_order

SELECT * FROM books WHERE author_id > 10 ORDER BY books.id DESC

Book.where(out_of_print: true).rewhere(out_of_print: false)

SELECT * FROM books WHERE out_of_print = 0

Book.where(out_of_print: true).where(out_of_print: false)

SELECT * FROM books WHERE out_of_print = 1 AND out_of_print = 0

Book.group(:author).regroup(:id)

SELECT * FROM books GROUP BY id

Book.group(:author).group(:id)

SELECT * FROM books GROUP BY author, id

Book.none # 返回一個空的關聯並且不執行任何查詢。

# 下面的 highlighted_reviews 方法預期始終返回一個關聯。
Book.first.highlighted_reviews.average(:rating)
# => 返回一本書的平均評分

class Book
  # 如果有至少 5 個評論，則返回評論，
  # 否則將其視為未評論的書籍
  def highlighted_reviews
    if reviews.count > 5
      reviews
    else
      Review.none # 尚未達到最低閾值
    end
  end
end

customer = Customer.readonly.first
customer.visits += 1
customer.save

c1 = Customer.find(1)
c2 = Customer.find(1)

c1.first_name = "Sandra"
c1.save

c2.first_name = "Michael"
c2.save # 引發 ActiveRecord::StaleObjectError

class Customer < ApplicationRecord
  self.locking_column = :lock_customer_column
end

Book.transaction do
  book = Book.lock.first
  book.title = 'Algorithms, second edition'
  book.save!
end

SQL (0.2ms)   BEGIN
Book Load (0.3ms)   SELECT * FROM books LIMIT 1 FOR UPDATE
Book Update (0.4ms)   UPDATE books SET updated_at = '2009-02-07 18:05:56', title = 'Algorithms, second edition' WHERE id = 1
SQL (0.8ms)   COMMIT

Book.transaction do
  book = Book.lock("LOCK IN SHARE MODE").find(1)
  book.increment!(:views)
end

book = Book.first
book.with_lock do
  # 此塊在事務中調用，
  # book 已經被鎖定。
  book.increment!(:views)
end

Author.joins("INNER JOIN books ON books.author_id = authors.id AND books.out_of_print = FALSE")

SELECT authors.* FROM authors INNER JOIN books ON books.author_id = authors.id AND books.out_of_print = FALSE

Book.joins(:reviews)

SELECT books.* FROM books
  INNER JOIN reviews ON reviews.book_id = books.id

Book.joins(:author, :reviews)

SELECT books.* FROM books
  INNER JOIN authors ON authors.id = books.author_id
  INNER JOIN reviews ON reviews.book_id = books.id

Book.joins(reviews: :customer)

SELECT books.* FROM books
  INNER JOIN reviews ON reviews.book_id = books.id
  INNER JOIN customers ON customers.id = reviews.customer_id

Author.joins(books: [{ reviews: { customer: :orders } }, :supplier])

SELECT * FROM authors
  INNER JOIN books ON books.author_id = authors.id
  INNER JOIN reviews ON reviews.book_id = books.id
  INNER JOIN customers ON customers.id = reviews.customer_id
  INNER JOIN orders ON orders.customer_id = customers.id
INNER JOIN suppliers ON suppliers.id = books.supplier_id

time_range = (Time.now.midnight - 1.day)..Time.now.midnight
Customer.joins(:orders).where('orders.created_at' => time_range).distinct

time_range = (Time.now.midnight - 1.day)..Time.now.midnight
Customer.joins(:orders).where(orders: { created_at: time_range }).distinct

class Order < ApplicationRecord
  belongs_to :customer

  scope :created_in_time_range, ->(time_range) {
    where(created_at: time_range)
  }
end

time_range = (Time.now.midnight - 1.day)..Time.now.midnight
Customer.joins(:orders).merge(Order.created_in_time_range(time_range)).distinct

Customer.left_outer_joins(:reviews).distinct.select('customers.*, COUNT(reviews.*) AS reviews_count').group('customers.id')

SELECT DISTINCT customers.*, COUNT(reviews.*) AS reviews_count FROM customers
LEFT OUTER JOIN reviews ON reviews.customer_id = customers.id GROUP BY customers.id

Customer.where.associated(:reviews)

SELECT customers.* FROM customers
INNER JOIN reviews ON reviews.customer_id = customers.id
WHERE reviews.id IS NOT NULL

Customer.where.missing(:reviews)

SELECT customers.* FROM customers
LEFT OUTER JOIN reviews ON reviews.customer_id = customers.id
WHERE reviews.id IS NULL

books = Book.limit(10)

books.each do |book|
  puts book.author.last_name
end

books = Book.includes(:author).limit(10)

books.each do |book|
  puts book.author.last_name
end

SELECT books.* FROM books LIMIT 10
SELECT authors.* FROM authors
  WHERE authors.book_id IN (1,2,3,4,5,6,7,8,9,10)

Customer.includes(:orders, :reviews)

Customer.includes(orders: { books: [:supplier, :author] }).find(1)

Author.includes(:books).where(books: { out_of_print: true })

  SELECT authors.id AS t0_r0, ... books.updated_at AS t1_r5 FROM authors LEFT OUTER JOIN books ON books.author_id = authors.id WHERE (books.out_of_print = 1)

Author.includes(:books).where("books.out_of_print = true").references(:books)

books = Book.preload(:author).limit(10)

books.each do |book|
  puts book.author.last_name
end

SELECT books.* FROM books LIMIT 10
SELECT authors.* FROM authors
  WHERE authors.book_id IN (1,2,3,4,5,6,7,8,9,10)

books = Book.eager_load(:author).limit(10)

books.each do |book|
  puts book.author.last_name
end

SELECT DISTINCT books.id FROM books LEFT OUTER JOIN authors ON authors.book_id = books.id LIMIT 10
SELECT books.id AS t0_r0, books.last_name AS t0_r1, ...
  FROM books LEFT OUTER JOIN authors ON authors.book_id = books.id
  WHERE books.id IN (1,2,3,4,5,6,7,8,9,10)

user = User.strict_loading.first
user.comments.to_a # 引發ActiveRecord::StrictLoadingViolationError

class Book < ApplicationRecord
  scope :out_of_print, -> { where(out_of_print: true) }
end

irb> Book.out_of_print
=> #<ActiveRecord::Relation> # 所有絕版的書籍

irb> author = Author.first
irb> author.books.out_of_print
=> #<ActiveRecord::Relation> # `author` 的所有絕版書籍

class Book < ApplicationRecord
  scope :out_of_print, -> { where(out_of_print: true) }
  scope :out_of_print_and_expensive, -> { out_of_print.where("price > 500") }
end

class Book < ApplicationRecord
  scope :costs_more_than, ->(amount) { where("price > ?", amount) }
end

irb> Book.costs_more_than(100.10)

class Book < ApplicationRecord
  def self.costs_more_than(amount)
    where("price > ?", amount)
  end
end

irb> author.books.costs_more_than(100.10)

class Order < ApplicationRecord
  scope :created_before, ->(time) { where(created_at: ...time) if time.present? }
end

class Order < ApplicationRecord
  def self.created_before(time)
    where(created_at: ...time) if time.present?
  end
end

class Book < ApplicationRecord
  default_scope { where(out_of_print: false) }
end

SELECT * FROM books WHERE (out_of_print = false)

class Book < ApplicationRecord
  def self.default_scope
    # 應該返回一個 ActiveRecord::Relation。
  end
end

class Book < ApplicationRecord
  default_scope { where(out_of_print: false) }
end

irb> Book.new
=> #<Book id: nil, out_of_print: false>
irb> Book.unscoped.new
=> #<Book id: nil, out_of_print: nil>

class Book < ApplicationRecord
  default_scope { where("out_of_print = ?", false) }
end

irb> Book.new
=> #<Book id: nil, out_of_print: nil>

class Book < ApplicationRecord
  scope :in_print, -> { where(out_of_print: false) }
  scope :out_of_print, -> { where(out_of_print: true) }

  scope :recent, -> { where(year_published: 50.years.ago.year..) }
  scope :old, -> { where(year_published: ...50.years.ago.year) }
end

irb> Book.out_of_print.old
SELECT books.* FROM books WHERE books.out_of_print = 'true' AND books.year_published < 1969

irb> Book.in_print.where(price: ...100)
SELECT books.* FROM books WHERE books.out_of_print = 'false' AND books.price < 100

irb> Book.in_print.merge(Book.out_of_print)
SELECT books.* FROM books WHERE books.out_of_print = true

class Book < ApplicationRecord
  default_scope { where(year_published: 50.years.ago.year..) }

  scope :in_print, -> { where(out_of_print: false) }
  scope :out_of_print, -> { where(out_of_print: true) }
end

irb> Book.all
SELECT books.* FROM books WHERE (year_published >= 1969)

irb> Book.in_print
SELECT books.* FROM books WHERE (year_published >= 1969) AND books.out_of_print = false

irb> Book.where('price > 50')
SELECT books.* FROM books WHERE (year_published >= 1969) AND (price > 50)

Book.unscoped.load

irb> Book.unscoped.all
SELECT books.* FROM books

irb> Book.where(out_of_print: true).unscoped.all
SELECT books.* FROM books

irb> Book.unscoped { Book.out_of_print }
SELECT books.* FROM books WHERE books.out_of_print

class Order < ApplicationRecord
  enum :status, [:shipped, :being_packaged, :complete, :cancelled]
end

irb> Order.shipped
=> #<ActiveRecord::Relation> # 所有status == :shipped的訂單
irb> Order.not_shipped
=> #<ActiveRecord::Relation> # 所有status != :shipped的訂單

irb> order = Order.shipped.first
irb> order.shipped?
=> true
irb> order.complete?
=> false

irb> order = Order.first
irb> order.shipped!
UPDATE "orders" SET "status" = ?, "updated_at" = ? WHERE "orders"."id" = ?  [["status", 0], ["updated_at", "2019-01-24 07:13:08.524320"], ["id", 1]]
=> true

Customer
  .select('customers.id, customers.last_name, reviews.body')
  .joins(:reviews)
  .where('reviews.created_at > ?', 1.week.ago)

SELECT customers.id, customers.last_name, reviews.body
FROM customers
INNER JOIN reviews
  ON reviews.customer_id = customers.id
WHERE (reviews.created_at > '2019-01-08')

Book
  .select('books.id, books.title, authors.first_name')
  .joins(:author)
  .find_by(title: 'Abstraction and Specification in Program Development')

SELECT books.id, books.title, authors.first_name
FROM books
INNER JOIN authors
  ON authors.id = books.author_id
WHERE books.title = $1 [["title", "Abstraction and Specification in Program Development"]]
LIMIT 1

irb> Customer.find_or_create_by(first_name: 'Andy')
=> #<Customer id: 5, first_name: "Andy", last_name: nil, title: nil, visits: 0, orders_count: nil, lock_version: 0, created_at: "2019-01-17 07:06:45", updated_at: "2019-01-17 07:06:45">

SELECT * FROM customers WHERE (customers.first_name = 'Andy') LIMIT 1
BEGIN
INSERT INTO customers (created_at, first_name, locked, orders_count, updated_at) VALUES ('2011-08-30 05:22:57', 'Andy', 1, NULL, '2011-08-30 05:22:57')
COMMIT

Customer.create_with(locked: false).find_or_create_by(first_name: 'Andy')

Customer.find_or_create_by(first_name: 'Andy') do |c|
  c.locked = false
end

validates :orders_count, presence: true

irb> Customer.find_or_create_by!(first_name: 'Andy')
ActiveRecord::RecordInvalid: Validation failed: Orders count can’t be blank

irb> nina = Customer.find_or_initialize_by(first_name: 'Nina')
=> #<Customer id: nil, first_name: "Nina", orders_count: 0, locked: true, created_at: "2011-08-30 06:09:27", updated_at: "2011-08-30 06:09:27">

irb> nina.persisted?
=> false

irb> nina.new_record?
=> true

SELECT * FROM customers WHERE (customers.first_name = 'Nina') LIMIT 1

irb> nina.save
=> true

irb> Customer.find_by_sql("SELECT * FROM customers INNER JOIN orders ON customers.id = orders.customer_id ORDER BY customers.created_at desc")
=> [#<Customer id: 1, first_name: "Lucas" ...>, #<Customer id: 2, first_name: "Jan" ...>, ...]

irb> Customer.connection.select_all("SELECT first_name, created_at FROM customers WHERE id = '1'").to_a
=> [{"first_name"=>"Rafael", "created_at"=>"2012-11-10 23:23:45.281189"}, {"first_name"=>"Eileen", "created_at"=>"2013-12-09 11:22:35.221282"}]

irb> Book.where(out_of_print: true).pluck(:id)
SELECT id FROM books WHERE out_of_print = true
=> [1, 2, 3]

irb> Order.distinct.pluck(:status)
SELECT DISTINCT status FROM orders
=> ["shipped", "being_packed", "cancelled"]

irb> Customer.pluck(:id, :first_name)
SELECT customers.id, customers.first_name FROM customers
=> [[1, "David"], [2, "Fran"], [3, "Jose"]]

Customer.select(:id).map { |c| c.id }
# 或者
Customer.select(:id).map(&:id)
# 或者
Customer.select(:id, :first_name).map { |c| [c.id, c.first_name] }

Customer.pluck(:id)
# 或者
Customer.pluck(:id, :first_name)

class Customer < ApplicationRecord
  def name
    "I am #{first_name}"
  end
end

irb> Customer.select(:first_name).map &:name
=> ["I am David", "I am Jeremy", "I am Jose"]

irb> Customer.pluck(:first_name)
=> ["David", "Jeremy", "Jose"]

irb> Order.joins(:customer, :books).pluck("orders.created_at, customers.email, books.title")

irb> Customer.pluck(:first_name).limit(1)
NoMethodError: undefined method `limit' for #<Array:0x007ff34d3ad6d8>

irb> Customer.limit(1).pluck(:first_name)
=> ["David"]

irb> assoc = Customer.includes(:reviews)
irb> assoc.pluck(:id)
SELECT "customers"."id" FROM "customers" LEFT OUTER JOIN "reviews" ON "reviews"."id" = "customers"."review_id"

irb> assoc.unscope(:includes).pluck(:id)

Customer.where(id: 1).pluck(:id).first

Customer.where(id: 1).pick(:id)

class Customer < ApplicationRecord
  self.primary_key = "customer_id"
end

irb> Customer.ids
SELECT customer_id FROM customers

Customer.exists?(1)

Customer.exists?(id: [1, 2, 3])
# 或者
Customer.exists?(first_name: ['Jane', 'Sergei'])

Customer.where(first_name: 'Ryan').exists?

Customer.exists?

# 通過模型
Order.any?
# SELECT 1 FROM orders LIMIT 1
Order.many?
# SELECT COUNT(*) FROM (SELECT 1 FROM orders LIMIT 2)

# 通過命名範圍
Order.shipped.any?
# SELECT 1 FROM orders WHERE orders.status = 0 LIMIT 1
Order.shipped.many?
# SELECT COUNT(*) FROM (SELECT 1 FROM orders WHERE orders.status = 0 LIMIT 2)

# 通過關聯
Book.where(out_of_print: true).any?
Book.where(out_of_print: true).many?

# 通過關聯
Customer.first.orders.any?
Customer.first.orders.many?

irb> Customer.count
SELECT COUNT(*) FROM customers

irb> Customer.where(first_name: 'Ryan').count
SELECT COUNT(*) FROM customers WHERE (first_name = 'Ryan')

irb> Customer.includes("orders").where(first_name: 'Ryan', orders: { status: 'shipped' }).count

SELECT COUNT(DISTINCT customers.id) FROM customers
  LEFT OUTER JOIN orders ON orders.customer_id = customers.id
  WHERE (customers.first_name = 'Ryan' AND orders.status = 0)

Order.average("subtotal")

Order.minimum("subtotal")

Order.maximum("subtotal")

Order.sum("subtotal")

Customer.where(id: 1).joins(:orders).explain

EXPLAIN SELECT `customers`.* FROM `customers` INNER JOIN `orders` ON `orders`.`customer_id` = `customers`.`id` WHERE `customers`.`id` = 1
+----+-------------+------------+-------+---------------+
| id | select_type | table      | type  | possible_keys |
+----+-------------+------------+-------+---------------+
|  1 | SIMPLE      | customers  | const | PRIMARY       |
|  1 | SIMPLE      | orders     | ALL   | NULL          |
+----+-------------+------------+-------+---------------+
+---------+---------+-------+------+-------------+
| key     | key_len | ref   | rows | Extra       |
+---------+---------+-------+------+-------------+
| PRIMARY | 4       | const |    1 |             |
| NULL    | NULL    | NULL  |    1 | Using where |
+---------+---------+-------+------+-------------+

2 rows in set (0.00 sec)

EXPLAIN SELECT "customers".* FROM "customers" INNER JOIN "orders" ON "orders"."customer_id" = "customers"."id" WHERE "customers"."id" = $1 [["id", 1]]
                                  QUERY PLAN
------------------------------------------------------------------------------
 Nested Loop  (cost=4.33..20.85 rows=4 width=164)
    ->  Index Scan using customers_pkey on customers  (cost=0.15..8.17 rows=1 width=164)
          Index Cond: (id = '1'::bigint)
    ->  Bitmap Heap Scan on orders  (cost=4.18..12.64 rows=4 width=8)
          Recheck Cond: (customer_id = '1'::bigint)
          ->  Bitmap Index Scan on index_orders_on_customer_id  (cost=0.00..4.18 rows=4 width=0)
                Index Cond: (customer_id = '1'::bigint)
(7 rows)

Customer.where(id: 1).includes(:orders).explain

EXPLAIN SELECT `customers`.* FROM `customers`  WHERE `customers`.`id` = 1
+----+-------------+-----------+-------+---------------+
| id | select_type | table     | type  | possible_keys |
+----+-------------+-----------+-------+---------------+
|  1 | SIMPLE      | customers | const | PRIMARY       |
+----+-------------+-----------+-------+---------------+
+---------+---------+-------+------+-------+
| key     | key_len | ref   | rows | Extra |
+---------+---------+-------+------+-------+
| PRIMARY | 4       | const |    1 |       |
+---------+---------+-------+------+-------+

1 row in set (0.00 sec)

EXPLAIN SELECT `orders`.* FROM `orders`  WHERE `orders`.`customer_id` IN (1)
+----+-------------+--------+------+---------------+
| id | select_type | table  | type | possible_keys |
+----+-------------+--------+------+---------------+
|  1 | SIMPLE      | orders | ALL  | NULL          |
+----+-------------+--------+------+---------------+
+------+---------+------+------+-------------+
| key  | key_len | ref  | rows | Extra       |
+------+---------+------+------+-------------+
| NULL | NULL    | NULL |    1 | Using where |
+------+---------+------+------+-------------+


1 row in set (0.00 sec)

  Customer Load (0.3ms)  SELECT "customers".* FROM "customers" WHERE "customers"."id" = $1  [["id", 1]]
  Order Load (0.3ms)  SELECT "orders".* FROM "orders" WHERE "orders"."customer_id" = $1  [["customer_id", 1]]
=> EXPLAIN SELECT "customers".* FROM "customers" WHERE "customers"."id" = $1 [["id", 1]]
                                    QUERY PLAN
----------------------------------------------------------------------------------
 Index Scan using customers_pkey on customers  (cost=0.15..8.17 rows=1 width=164)
   Index Cond: (id = '1'::bigint)
(2 rows)

Customer.where(id: 1).joins(:orders).explain(:analyze, :verbose)

EXPLAIN (ANALYZE, VERBOSE) SELECT "shop_accounts".* FROM "shop_accounts" INNER JOIN "customers" ON "customers"."id" = "shop_accounts"."customer_id" WHERE "shop_accounts"."id" = $1 [["id", 1]]
                                                                   QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.30..16.37 rows=1 width=24) (actual time=0.003..0.004 rows=0 loops=1)
   Output: shop_accounts.id, shop_accounts.customer_id, shop_accounts.customer_carrier_id
   Inner Unique: true
   ->  Index Scan using shop_accounts_pkey on public.shop_accounts  (cost=0.15..8.17 rows=1 width=24) (actual time=0.003..0.003 rows=0 loops=1)
         Output: shop_accounts.id, shop_accounts.customer_id, shop_accounts.customer_carrier_id
         Index Cond: (shop_accounts.id = '1'::bigint)
   ->  Index Only Scan using customers_pkey on public.customers  (cost=0.15..8.17 rows=1 width=8) (never executed)
         Output: customers.id
         Index Cond: (customers.id = shop_accounts.customer_id)
         Heap Fetches: 0
 Planning Time: 0.063 ms
 Execution Time: 0.011 ms
(12 rows)

Customer.where(id: 1).joins(:orders).explain(:analyze)

ANALYZE SELECT `shop_accounts`.* FROM `shop_accounts` INNER JOIN `customers` ON `customers`.`id` = `shop_accounts`.`customer_id` WHERE `shop_accounts`.`id` = 1
+----+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+--------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | r_rows | filtered | r_filtered | Extra                          |
+----+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+--------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | NULL   | NULL     | NULL       | no matching row in const table |
+----+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+--------------------------------+
1 row in set (0.00 sec)