Guides.rubyonrails.org

class Client < ActiveRecord::Base
  has_one  :address
  has_many :orders
  has_and_belongs_to_many :roles
end

class Address < ActiveRecord::Base
  belongs_to :client
end

class Order < ActiveRecord::Base
  belongs_to :client, counter_cache: true
end

class Role < ActiveRecord::Base
  has_and_belongs_to_many :clients
end

# Find the client with primary key (id) 10.
client = Client.find(10)
# => #<Client id: 10, first_name: "Ryan">

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

# Find the clients with primary keys 1 and 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 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.find_by first_name: 'Lifo'
# => #<Client id: 1, first_name: "Lifo">

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

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

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

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

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

SELECT * FROM clients ORDER BY id ASC LIMIT 2

Client.last(2)
# => [#<Client id: 10, first_name: "Ryan">,
      #<Client id: 9, first_name: "John">]

SELECT * FROM clients ORDER BY id DESC LIMIT 2

# 如果有數千個使用者，效率非常差。
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

# Give add_invoices an array of 1000 invoices at a time
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)

Client.where('locked' => true)

Address.where(client: client)
Address.joins(:clients).where(clients: {address: address})

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

Article.where.not(author: author)

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

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

Client.order(orders_count: :asc, created_at: :desc)
# OR
Client.order(:orders_count, created_at: :desc)
# OR
Client.order("orders_count ASC, created_at DESC")
# OR
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
# => Returns unique names

query.distinct(false)
# => Returns all names, even if there are duplicates

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

# Original query without `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

# Original query without `only`
SELECT "articles".* FROM "articles" WHERE (id > 10) ORDER BY id desc LIMIT 20

class Article < ActiveRecord::Base
  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.none # returns an empty Relation and fires no queries.

# The visible_articles method below is expected to return a 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 # => returning [] or nil breaks the caller code in this case
  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 # Raises an ActiveRecord::StaleObjectError

class Client < ActiveRecord::Base
  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
  # This block is called within a transaction,
  # item is already locked.
  item.increment!(:views)
end

Client.joins('LEFT OUTER JOIN addresses ON addresses.client_id = clients.id')

SELECT clients.* FROM clients LEFT OUTER JOIN addresses ON addresses.client_id = clients.id

class Category < ActiveRecord::Base
  has_many :articles
end

class Article < ActiveRecord::Base
  belongs_to :category
  has_many :comments
  has_many :tags
end

class Comment < ActiveRecord::Base
  belongs_to :article
  has_one :guest
end

class Guest < ActiveRecord::Base
  belongs_to :comment
end

class Tag < ActiveRecord::Base
  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})

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 < ActiveRecord::Base
  scope :published, -> { where(published: true) }
end

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

class Article < ActiveRecord::Base
  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 < ActiveRecord::Base
  scope :created_before, ->(time) { where("created_at < ?", time) }
end

Article.created_before(Time.zone.now)

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

category.articles.created_before(time)

class User < ActiveRecord::Base
  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 < ActiveRecord::Base
  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" = 'active'

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

class Client < ActiveRecord::Base
  default_scope { where("removed_at IS NULL") }
end

SELECT * FROM clients WHERE removed_at IS NULL

class Client < ActiveRecord::Base
  def self.default_scope
    # Should return an ActiveRecord::Relation.
  end
end

Client.unscoped.load

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

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 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 }
# or
Client.select(:id).map(&:id)
# or
Client.select(:id, :name).map { |c| [c.id, c.name] }

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

class Client < ActiveRecord::Base
  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 < ActiveRecord::Base
  self.primary_key = "person_id"
end

Person.ids
# SELECT person_id FROM people

Client.exists?(1)

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

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

Client.exists?

# via a model
Article.any?
Article.many?

# via a named scope
Article.recent.any?
Article.recent.many?

# via a relation
Article.where(published: true).any?
Article.where(published: true).many?

# via an association
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.average(:orders_count)

Client.minimum("age")
Client.minimum(:age)

Client.maximum("age")
Client.maximum(:age)

Client.sum("orders_count")
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)