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) } # 50년 이상 된 책
  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

# primary key(id)가 10인 customer를 찾습니다.
irb> customer = Customer.find(10)
=> #<Customer id: 10, first_name: "Ryan">

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

# primary key가 1과 10인 customer를 찾습니다.
irb> customers = Customer.find([1, 10]) # 또는 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))

# store_id가 3이고 id가 17인 customer를 찾음
irb> customers = Customer.find([3, 17])
=> #<Customer store_id: 3, id: 17, first_name: "Magda">

SELECT * FROM customers WHERE store_id = 3 AND id = 17

# primary key가 [1, 8] 및 [7, 15]인 customer를 찾습니다.
irb> customers = Customer.find([[1, 8], [7, 15]]) # 또는 Customer.find([1, 8], [7, 15])
=> [#<Customer store_id: 1, id: 8, first_name: "Pat">, #<Customer store_id: 7, id: 15, first_name: "Chris">]

SELECT * FROM customers WHERE (store_id = 1 AND id = 8 OR store_id = 7 AND id = 15)

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

irb> customers = Customer.first(3)
=> [#<Customer id: 1, first_name: "Lifo">, #<Customer id: 2, first_name: "Fifo">, #<Customer id: 3, first_name: "Filo">]

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

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

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

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> customer = Customer.last
=> #<Customer id: 221, store_id: 1, first_name: "Lifo">

SELECT * FROM customers ORDER BY customers.store_id DESC, 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!

irb> customer = Customer.last
=> #<Customer id: 10, store_id: 5, first_name: "Joe"> 
irb> Customer.find_by(id: customer.id) # Customer.find_by(id: [5, 10])
=> #<Customer id: 5, store_id: 3, first_name: "Bob">

irb> customer = Customer.last
=> #<Customer id: 10, store_id: 5, first_name: "Joe">
irb> Customer.find_by(id: customer.id_value) # Customer.find_by(id: 10)
=> #<Customer id: 10, store_id: 5, first_name: "Joe">

# 테이블이 큰 경우 너무 많은 메모리를 소비할 수 있습니다.
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(start: 2000, finish: 10000) 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)

author = Author.first
Book.where(author: author)
Author.joins(:books).where(books: { author: author })

Book.where([:author_id, :id] => [[15, 1], [15, 2]])

class Book < ApplicationRecord
  self.primary_key = [:author_id, :id]
end

Book.where(Book.primary_key => [[2, 1], [3, 1]])

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

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

Client.where(created_at: ['2008-12-21 00:00:00', '2008-12-22 00:00:00'])

Student.where(response_time: nil..Float::INFINITY)

SELECT * FROM students WHERE (students.response_time <= INFINITY OR students.response_time IS NULL)

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.includes(:author).order(books: { print_year: :desc }, authors: { name: :asc })
# 또는
Book.includes(:author).order("books.print_year desc", "authors.name asc")

Book.select(:isbn, :out_of_print) 
# 또는
Book.select("isbn, out_of_print")

SELECT isbn, out_of_print FROM books

ActiveModel::MissingAttributeError: Book 모델에서 '<attribute>' 속성이 누락되었습니다

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} # 포장 중인 주문이 7개, 배송된 주문이 12개

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

Order.select("created_at을 ordered_date으로, sum(total)을 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 객체의 total price를 반환합니다

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)
# books.* FROM books에서 out_of_print = 0인 레코드 SELECT

Book.order("id desc").merge(Book.unscope(:order))
# books 테이블에서 모든 컬럼을 조회

Post.where('id > 10').limit(20).order('id asc').unscope(:order)

SELECT "posts".* FROM "posts" WHERE (id > 10) LIMIT 20

# `order` 부분이 제거되었음을 주목하세요

Post.where(id: 10, trashed: false).unscope(where: :id)
# SELECT "posts".* FROM "posts" WHERE trashed = false

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

Article.where(trashed: true).rewhere(trashed: false)

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 # 빈 Relation을 반환하고 쿼리를 실행하지 않습니다.

# 아래의 highlighted_reviews 메서드는 항상 Relation을 반환하도록 기대됩니다.
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 # ActiveRecord::ReadOnlyRecord 에러를 발생시킴

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은 이미 lock 상태입니다.
  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])

class Author < ApplicationRecord
  has_many :books
  has_many :authors_books
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

SELECT authors.* 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.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.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 "books"."id" AS t0_r0, "books"."title" AS t0_r1, ... FROM "books"
  LEFT OUTER JOIN "authors" ON "authors"."id" = "books"."author_id" 
  LIMIT 10

user = User.strict_loading.first
user.address.city # ActiveRecord::StrictLoadingViolationError를 발생시킵니다
user.comments.to_a # ActiveRecord::StrictLoadingViolationError를 발생시킵니다

user = User.first
user.strict_loading!
user.address.city # ActiveRecord::StrictLoadingViolationError를 발생시킴
user.comments.to_a # ActiveRecord::StrictLoadingViolationError를 발생시킴

user.strict_loading!(mode: :n_plus_one_only)
user.address.city # => "Tatooine" 
user.comments.to_a # => [#<Comment:0x00...]
user.comments.first.likes.to_a # ActiveRecord::StrictLoadingViolationError를 발생시킵니다

class Author < ApplicationRecord
  has_many :books, strict_loading: true
end

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의 절판된 모든 book들

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? # time이 존재하는 경우 해당 시간 이전에 생성된 주문들을 반환합니다
  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..) } # 최근 50년 이내 출판된 도서
  scope :old, -> { where(year_published: ...50.years.ago.year) } # 50년 이전에 출판된 도서
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
  # 50년 전부터 현재까지 출판된 도서만 기본으로 조회합니다
  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를 실행하고 즉시 레코드를 로드합니다.

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 = true

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

irb> Order.shipped
=> #<ActiveRecord::Relation> # status == :shipped인 모든 orders
irb> Order.not_shipped  
=> #<ActiveRecord::Relation> # status != :shipped인 모든 orders

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">

customers에서 (customers.first_name = 'Andy')인 모든 컬럼을 LIMIT 1로 선택
BEGIN
customers 테이블에 (created_at, first_name, locked, orders_count, updated_at) 컬럼에 ('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: 유효성 검사 실패: Orders count는 비워둘 수 없습니다

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.lease_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] }

# calls Book.create with a block
Book.destroy_all

book = Book.create do |book|
  book.name = "Blink"
  book.title = "Blink"
  book.author = "Malcolm Gladwell"
end

5.times do
  Book.create do |book|
    book.name = "Blink"
    book.title = "Blink"
    book.author = "Malcolm Gladwell"
  end
end

Customer.pluck(:id)
# 또는
Customer.pluck(:id, :first_name)

class Customer < ApplicationRecord
  def name
    "나는 #{first_name}입니다"
  end
end

irb> Customer.select(:first_name).map &:name
=> ["나는 David입니다", "나는 Jeremy입니다", "나는 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

with_options prefix: 'admin' do |admin|
  admin.resources :posts
  admin.resources :comments
end

resources :posts, prefix: 'admin'
resources :comments, prefix: 'admin'

      admin_posts GET       /admin/posts(.:format)          posts#index
                  POST      /admin/posts(.:format)          posts#create
   new_admin_post GET       /admin/posts/new(.:format)      posts#new
  edit_admin_post GET       /admin/posts/:id/edit(.:format) posts#edit
       admin_post GET       /admin/posts/:id(.:format)      posts#show
                  PATCH/PUT /admin/posts/:id(.:format)      posts#update
                  DELETE    /admin/posts/:id(.:format)      posts#destroy

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

irb> Customer.ids
SELECT id FROM customers

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?

# model을 통해
Order.any?
# SELECT 1 FROM orders LIMIT 1
Order.many?
# SELECT COUNT(*) FROM (SELECT 1 FROM orders LIMIT 2)

# named scope을 통해 
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)

# relation을 통해
Book.where(out_of_print: true).any?
Book.where(out_of_print: true).many?

# association을 통해
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개의 행이 설정됨 (0.00 초)

EXPLAIN SELECT "customers".* FROM "customers" INNER JOIN "orders" ON "orders"."customer_id" = "customers"."id" WHERE "customers"."id" = $1 [["id", 1]]
                                  실행 계획
------------------------------------------------------------------------------
 Nested Loop (비용=4.33..20.85 행=4 너비=164)
    ->  Index Scan using customers_pkey on customers (비용=0.15..8.17 행=1 너비=164)
          Index Cond: (id = '1'::bigint)
    ->  Bitmap Heap Scan on orders (비용=4.18..12.64 행=4 너비=8)
          Recheck Cond: (customer_id = '1'::bigint)
          ->  Bitmap Index Scan on index_orders_on_customer_id (비용=0.00..4.18 행=4 너비=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개의 행이 집합에 있습니다 (0.00 초)

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개의 행이 집합에 있습니다 (0.00 초)

  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]]
                                    쿼리 계획
----------------------------------------------------------------------------------
 인덱스 스캔: customers_pkey를 사용하여 customers 테이블에 대해  (비용=0.15..8.17 rows=1 width=164)
   인덱스 조건: (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 (비용=0.30..16.37 행=1 너비=24) (실제 시간=0.003..0.004 행=0 반복=1)
   출력: shop_accounts.id, shop_accounts.customer_id, shop_accounts.customer_carrier_id 
   내부 유일: true
   ->  인덱스 스캔 using shop_accounts_pkey on public.shop_accounts (비용=0.15..8.17 행=1 너비=24) (실제 시간=0.003..0.003 행=0 반복=1)
         출력: shop_accounts.id, shop_accounts.customer_id, shop_accounts.customer_carrier_id
         인덱스 조건: (shop_accounts.id = '1'::bigint)
   ->  인덱스 전용 스캔 using customers_pkey on public.customers (비용=0.15..8.17 행=1 너비=8) (실행되지 않음)
         출력: customers.id
         인덱스 조건: (customers.id = shop_accounts.customer_id)
         힙 가져오기: 0
 계획 시간: 0.063 ms
 실행 시간: 0.011 ms
(12 rows)