Guides.rubyonrails.org

class Client < ApplicationRecord
  has_one :address
  has_many :orders
  has_and_belongs_to_many :roles
end

class Address < ApplicationRecord
  belongs_to :client
end

class Order < ApplicationRecord
  belongs_to :client, counter_cache: true
end

class Role < ApplicationRecord
  has_and_belongs_to_many :clients
end

# 查找主键（ID）为 10 的客户
client = Client.find(10)
# => #<Client id: 10, first_name: "Ryan">

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

# 查找主键为 1 和 10 的客户
client = Client.find([1, 10]) # Or even Client.find(1, 10)
# => [#<Client id: 1, first_name: "Lifo">, #<Client id: 10, first_name: "Ryan">]

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

client = Client.take
# => #<Client id: 1, first_name: "Lifo">

SELECT * FROM clients LIMIT 1

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

SELECT * FROM clients LIMIT 2

client = Client.first
# => #<Client id: 1, first_name: "Lifo">

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

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

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

client = Client.order(:first_name).first
# => #<Client id: 2, first_name: "Fifo">

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

client = Client.last
# => #<Client id: 221, first_name: "Russel">

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

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

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

client = Client.order(:first_name).last
# => #<Client id: 220, first_name: "Sara">

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

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

Client.find_by first_name: 'Jon'
# => nil

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

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

Client.find_by! first_name: 'does not exist'
# => ActiveRecord::RecordNotFound

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

# 如果 users 表有几千行记录，这样做效率很低
User.all.each do |user|
  NewsMailer.weekly(user).deliver_now
end

User.find_each do |user|
  NewsMailer.weekly(user).deliver_now
end

User.where(weekly_subscriber: true).find_each do |user|
  NewsMailer.weekly(user).deliver_now
end

User.find_each(batch_size: 5000) do |user|
  NewsMailer.weekly(user).deliver_now
end

User.find_each(start: 2000, batch_size: 5000) do |user|
  NewsMailer.weekly(user).deliver_now
end

User.find_each(start: 2000, finish: 10000, batch_size: 5000) do |user|
  NewsMailer.weekly(user).deliver_now
end

# 一次把 1000 张发票组成的数组传给 add_invoices
Invoice.find_in_batches do |invoices|
  export.add_invoices(invoices)
end

Client.where("orders_count = ?", params[:orders])

Client.where("orders_count = ? AND locked = ?", params[:orders], false)

Client.where("orders_count = ?", params[:orders])

Client.where("orders_count = #{params[:orders]}")

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

Client.where(locked: true)

SELECT * FROM clients WHERE (clients.locked = 1)

Client.where('locked' => true)

Article.where(author: author)
Author.joins(:articles).where(articles: { author: author })

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(orders_count: [1,3,5])

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

Client.where.not(locked: true)

SELECT * FROM clients WHERE (clients.locked != 1)

Client.order(:created_at)
# 或
Client.order("created_at")

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

Client.order(orders_count: :asc, created_at: :desc)
# 或
Client.order(:orders_count, created_at: :desc)
# 或
Client.order("orders_count ASC, created_at DESC")
# 或
Client.order("orders_count ASC", "created_at DESC")

Client.order("orders_count ASC").order("created_at DESC")
# SELECT * FROM clients ORDER BY orders_count ASC, created_at DESC

Client.select("viewable_by, locked")

SELECT viewable_by, locked FROM clients

ActiveModel::MissingAttributeError: missing attribute: <attribute>

Client.select(:name).distinct

SELECT DISTINCT name FROM clients

query = Client.select(:name).distinct
# => 返回无重复的名字

query.distinct(false)
# => 返回所有名字，即使有重复

Client.limit(5)

SELECT * FROM clients LIMIT 5

Client.limit(5).offset(30)

SELECT * FROM clients LIMIT 5 OFFSET 30

Order.select("date(created_at) as ordered_date, sum(price) as total_price").group("date(created_at)")

SELECT date(created_at) as ordered_date, sum(price) as total_price
FROM orders
GROUP BY date(created_at)

Order.group(:status).count
# => { 'awaiting_approval' => 7, 'paid' => 12 }

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

Order.select("date(created_at) as ordered_date, sum(price) as total_price").
  group("date(created_at)").having("sum(price) > ?", 100)

SELECT date(created_at) as ordered_date, sum(price) as total_price
FROM orders
GROUP BY date(created_at)
HAVING sum(price) > 100

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

SELECT * FROM articles WHERE id > 10 LIMIT 20

# 没使用 `unscope` 之前的查询
SELECT * FROM articles WHERE id > 10 ORDER BY id asc LIMIT 20

Article.where(id: 10, trashed: false).unscope(where: :id)
# SELECT "articles".* FROM "articles" WHERE trashed = 0

Article.order('id asc').merge(Article.unscope(:order))
# SELECT "articles".* FROM "articles"

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

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

# 没使用 `only` 之前的查询
SELECT "articles".* FROM "articles" WHERE (id > 10) ORDER BY id desc LIMIT 20

class Article < ApplicationRecord
  has_many :comments, -> { order('posted_at DESC') }
end

Article.find(10).comments.reorder('name')

SELECT * FROM articles WHERE id = 10
SELECT * FROM comments WHERE article_id = 10 ORDER BY name

SELECT * FROM articles WHERE id = 10
SELECT * FROM comments WHERE article_id = 10 ORDER BY posted_at DESC

Client.where("orders_count > 10").order(:name).reverse_order

SELECT * FROM clients WHERE orders_count > 10 ORDER BY name DESC

Client.where("orders_count > 10").reverse_order

SELECT * FROM clients WHERE orders_count > 10 ORDER BY clients.id DESC

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

SELECT * FROM articles WHERE `trashed` = 0

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

SELECT * FROM articles WHERE `trashed` = 1 AND `trashed` = 0

Article.none # 返回一个空 Relation 对象，而且不执行查询

# 下面的 visible_articles 方法期待返回一个空 Relation 对象
@articles = current_user.visible_articles.where(name: params[:name])

def visible_articles
  case role
  when 'Country Manager'
    Article.where(country: country)
  when 'Reviewer'
    Article.published
  when 'Bad User'
    Article.none # => 如果这里返回 [] 或 nil，会导致调用方出错
  end
end

client = Client.readonly.first
client.visits += 1
client.save

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

c1.first_name = "Michael"
c1.save

c2.name = "should fail"
c2.save # 抛出 ActiveRecord::StaleObjectError

class Client < ApplicationRecord
  self.locking_column = :lock_client_column
end

Item.transaction do
  i = Item.lock.first
  i.name = 'Jones'
  i.save!
end

SQL (0.2ms)   BEGIN
Item Load (0.3ms)   SELECT * FROM `items` LIMIT 1 FOR UPDATE
Item Update (0.4ms)   UPDATE `items` SET `updated_at` = '2009-02-07 18:05:56', `name` = 'Jones' WHERE `id` = 1
SQL (0.8ms)   COMMIT

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

item = Item.first
item.with_lock do
  # 这个块在事务中调用
  # item 已经锁定
  item.increment!(:views)
end

Author.joins("INNER JOIN posts ON posts.author_id = author.id AND posts.published = 't'")

SELECT clients.* FROM clients INNER JOIN posts ON posts.author_id = author.id AND posts.published = 't'

class Category < ApplicationRecord
  has_many :articles
end

class Article < ApplicationRecord
  belongs_to :category
  has_many :comments
  has_many :tags
end

class Comment < ApplicationRecord
  belongs_to :article
  has_one :guest
end

class Guest < ApplicationRecord
  belongs_to :comment
end

class Tag < ApplicationRecord
  belongs_to :article
end

Category.joins(:articles)

SELECT categories.* FROM categories
  INNER JOIN articles ON articles.category_id = categories.id

Article.joins(:category, :comments)

SELECT articles.* FROM articles
  INNER JOIN categories ON articles.category_id = categories.id
  INNER JOIN comments ON comments.article_id = articles.id

Article.joins(comments: :guest)

SELECT articles.* FROM articles
  INNER JOIN comments ON comments.article_id = articles.id
  INNER JOIN guests ON guests.comment_id = comments.id

Category.joins(articles: [{ comments: :guest }, :tags])

SELECT categories.* FROM categories
  INNER JOIN articles ON articles.category_id = categories.id
  INNER JOIN comments ON comments.article_id = articles.id
  INNER JOIN guests ON guests.comment_id = comments.id
  INNER JOIN tags ON tags.article_id = articles.id

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

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

Author.left_outer_joins(:posts).distinct.select('authors.*, COUNT(posts.*) AS posts_count').group('authors.id')

SELECT DISTINCT authors.*, COUNT(posts.*) AS posts_count FROM "authors"
LEFT OUTER JOIN posts ON posts.author_id = authors.id GROUP BY authors.id

clients = Client.limit(10)

clients.each do |client|
  puts client.address.postcode
end

clients = Client.includes(:address).limit(10)

clients.each do |client|
  puts client.address.postcode
end

SELECT * FROM clients LIMIT 10
SELECT addresses.* FROM addresses
  WHERE (addresses.client_id IN (1,2,3,4,5,6,7,8,9,10))

Article.includes(:category, :comments)

Category.includes(articles: [{ comments: :guest }, :tags]).find(1)

Article.includes(:comments).where(comments: { visible: true })

SELECT "articles"."id" AS t0_r0, ... "comments"."updated_at" AS t1_r5 FROM "articles" LEFT OUTER JOIN "comments" ON "comments"."article_id" = "articles"."id" WHERE (comments.visible = 1)

Article.includes(:comments).where("comments.visible = true").references(:comments)

class Article < ApplicationRecord
  scope :published, -> { where(published: true) }
end

class Article < ApplicationRecord
  def self.published
    where(published: true)
  end
end

class Article < ApplicationRecord
  scope :published,               -> { where(published: true) }
  scope :published_and_commented, -> { published.where("comments_count > 0") }
end

Article.published # => [published articles]

category = Category.first
category.articles.published # => [published articles belonging to this category]

class Article < ApplicationRecord
  scope :created_before, ->(time) { where("created_at < ?", time) }
end

Article.created_before(Time.zone.now)

class Article < ApplicationRecord
  def self.created_before(time)
    where("created_at < ?", time)
  end
end

category.articles.created_before(time)

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

class Article < ApplicationRecord
  def self.created_before(time)
    where("created_at < ?", time) if time.present?
  end
end

class Client < ApplicationRecord
  default_scope { where("removed_at IS NULL") }
end

SELECT * FROM clients WHERE removed_at IS NULL

class Client < ApplicationRecord
  def self.default_scope
    # 应该返回一个 ActiveRecord::Relation 对象
  end
end

class Client < ApplicationRecord
 default_scope { where(active: true) }
end

Client.new          # => #<Client id: nil, active: true>
Client.unscoped.new # => #<Client id: nil, active: nil>

class User < ApplicationRecord
  scope :active, -> { where state: 'active' }
  scope :inactive, -> { where state: 'inactive' }
end

User.active.inactive
# SELECT "users".* FROM "users" WHERE "users"."state" = 'active' AND "users"."state" = 'inactive'

User.active.where(state: 'finished')
# SELECT "users".* FROM "users" WHERE "users"."state" = 'active' AND "users"."state" = 'finished'

User.active.merge(User.inactive)
# SELECT "users".* FROM "users" WHERE "users"."state" = 'inactive'

class User < ApplicationRecord
  default_scope { where state: 'pending' }
  scope :active, -> { where state: 'active' }
  scope :inactive, -> { where state: 'inactive' }
end

User.all
# SELECT "users".* FROM "users" WHERE "users"."state" = 'pending'

User.active
# SELECT "users".* FROM "users" WHERE "users"."state" = 'pending' AND "users"."state" = 'active'

User.where(state: 'inactive')
# SELECT "users".* FROM "users" WHERE "users"."state" = 'pending' AND "users"."state" = 'inactive'

Client.unscoped.load

Client.unscoped.all
# SELECT "clients".* FROM "clients"

Client.where(published: false).unscoped.all
# SELECT "clients".* FROM "clients"

Client.unscoped {
  Client.created_before(Time.zone.now)
}

class Book < ApplicationRecord
  enum availability: [:available, :unavailable]
end

# 下面的示例只查询可用的图书
Book.available
# 或
Book.where(availability: :available)

book = Book.new(availability: :available)
book.available?   # => true
book.unavailable! # => true
book.available?   # => false

Person
  .select('people.id, people.name, comments.text')
  .joins(:comments)
  .where('comments.created_at > ?', 1.week.ago)

SELECT people.id, people.name, comments.text
FROM people
INNER JOIN comments
  ON comments.person_id = people.id
WHERE comments.created_at = '2015-01-01'

Person
  .select('people.id, people.name, companies.name')
  .joins(:company)
  .find_by('people.name' => 'John') # this should be the last

SELECT people.id, people.name, companies.name
FROM people
INNER JOIN companies
  ON companies.person_id = people.id
WHERE people.name = 'John'
LIMIT 1

Client.find_or_create_by(first_name: 'Andy')
# => #<Client id: 1, first_name: "Andy", orders_count: 0, locked: true, created_at: "2011-08-30 06:09:27", updated_at: "2011-08-30 06:09:27">

SELECT * FROM clients WHERE (clients.first_name = 'Andy') LIMIT 1
BEGIN
INSERT INTO clients (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

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

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

validates :orders_count, presence: true

Client.find_or_create_by!(first_name: 'Andy')
# => ActiveRecord::RecordInvalid: Validation failed: Orders count can't be blank

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

nick.persisted?
# => false

nick.new_record?
# => true

SELECT * FROM clients WHERE (clients.first_name = 'Nick') LIMIT 1

nick.save
# => true

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

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

Client.where(active: true).pluck(:id)
# SELECT id FROM clients WHERE active = 1
# => [1, 2, 3]

Client.distinct.pluck(:role)
# SELECT DISTINCT role FROM clients
# => ['admin', 'member', 'guest']

Client.pluck(:id, :name)
# SELECT clients.id, clients.name FROM clients
# => [[1, 'David'], [2, 'Jeremy'], [3, 'Jose']]

Client.select(:id).map { |c| c.id }
# 或
Client.select(:id).map(&:id)
# 或
Client.select(:id, :name).map { |c| [c.id, c.name] }

Client.pluck(:id)
# 或
Client.pluck(:id, :name)

class Client < ApplicationRecord
  def name
    "I am #{super}"
  end
end

Client.select(:name).map &:name
# => ["I am David", "I am Jeremy", "I am Jose"]

Client.pluck(:name)
# => ["David", "Jeremy", "Jose"]

Client.pluck(:name).limit(1)
# => NoMethodError: undefined method `limit' for #<Array:0x007ff34d3ad6d8>

Client.limit(1).pluck(:name)
# => ["David"]

Person.ids
# SELECT id FROM people

class Person < ApplicationRecord
  self.primary_key = "person_id"
end

Person.ids
# SELECT person_id FROM people

Client.exists?(1)

Client.exists?(id: [1,2,3])
# 或
Client.exists?(name: ['John', 'Sergei'])

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

Client.exists?

# 通过模型
Article.any?
Article.many?

# 通过指定的作用域
Article.recent.any?
Article.recent.many?

# 通过关系
Article.where(published: true).any?
Article.where(published: true).many?

# 通过关联
Article.first.categories.any?
Article.first.categories.many?

Client.count
# SELECT count(*) AS count_all FROM clients

Client.where(first_name: 'Ryan').count
# SELECT count(*) AS count_all FROM clients WHERE (first_name = 'Ryan')

Client.includes("orders").where(first_name: 'Ryan', orders: { status: 'received' }).count

SELECT count(DISTINCT clients.id) AS count_all FROM clients
  LEFT OUTER JOIN orders ON orders.client_id = client.id WHERE
  (clients.first_name = 'Ryan' AND orders.status = 'received')

Client.average("orders_count")

Client.minimum("age")

Client.maximum("age")

Client.sum("orders_count")

User.where(id: 1).joins(:articles).explain

EXPLAIN for: SELECT `users`.* FROM `users` INNER JOIN `articles` ON `articles`.`user_id` = `users`.`id` WHERE `users`.`id` = 1
+----+-------------+----------+-------+---------------+
| id | select_type | table    | type  | possible_keys |
+----+-------------+----------+-------+---------------+
|  1 | SIMPLE      | users    | const | PRIMARY       |
|  1 | SIMPLE      | articles | 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 for: SELECT "users".* FROM "users" INNER JOIN "articles" ON "articles"."user_id" = "users"."id" WHERE "users"."id" = 1
                                  QUERY PLAN
------------------------------------------------------------------------------
 Nested Loop Left Join  (cost=0.00..37.24 rows=8 width=0)
   Join Filter: (articles.user_id = users.id)
   ->  Index Scan using users_pkey on users  (cost=0.00..8.27 rows=1 width=4)
         Index Cond: (id = 1)
   ->  Seq Scan on articles  (cost=0.00..28.88 rows=8 width=4)
         Filter: (articles.user_id = 1)
(6 rows)

User.where(id: 1).includes(:articles).explain

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

1 row in set (0.00 sec)

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


1 row in set (0.00 sec)