railsguides.jp

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のクライアントを検索
clients = Client.find(10)
# => #<Client id: 10, first_name: "Ryan">

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

# 主キー（id）が1と10のクライアントを検索
clients = Client.find([1, 10]) # 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

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

SELECT * FROM clients LIMIT 2

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

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

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

clients = 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!

# このコードはテーブルが大きい場合、メモリを大量に消費する可能性あり
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) do |user|
  NewsMailer.weekly(user).deliver_now
end

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

# 1回あたりadd_invoicesに納品書1000通の配列を渡す
Invoice.find_in_batches do |invoices|
  export.add_invoices(invoices)
end

Invoice.pending.find_in_batches do |invoices|
  pending_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.where(locked: true).or(Client.where(orders_count: [1,3,5]))

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

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: <属性名>

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 # 空のリレーションを返し、クエリを生成しない。

# visible_articles メソッドはリレーションを返すことが期待されている
@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 = authors.id AND posts.published = 't'")

SELECT authors.* FROM authors INNER JOIN posts ON posts.author_id = authors.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 # => [このカテゴリに属する、公開済みの記事]

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 Client < ApplicationRecord
  default_scope { where("active = ?", true) }
end

Client.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') # 名を指定

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'").to_hash
# => [
#   {"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
    "私は#{super}"
  end
end

Client.select(:name).map &:name
# => ["私はDavid", "私はJeremy", "私は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?

# via a model
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 = clients.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)