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

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

SELECT * FROM clients LIMIT 1

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

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

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

SELECT * FROM clients ORDER BY clients.id 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

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

SELECT * FROM clients LIMIT 1

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

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

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

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

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

Client.find_by! first_name: 'Jon'
# => ActiveRecord::RecordNotFound

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

# 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.take(2)
# => [#<Client id: 1, first_name: "Lifo">,
      #<Client id: 2, first_name: "Raf">]

SELECT * FROM clients LIMIT 2

Client.first(2)
# => [#<Client id: 1, first_name: "Lifo">,
      #<Client id: 2, first_name: "Raf">]

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

# This is very inefficient when the users table has thousands of rows.
User.all.each do |user|
  NewsLetter.weekly_deliver(user)
end

User.find_each do |user|
  NewsLetter.weekly_deliver(user)
end

User.find_each(batch_size: 5000) do |user|
  NewsLetter.weekly_deliver(user)
end

User.find_each(start: 2000, batch_size: 5000) do |user|
  NewsLetter.weekly_deliver(user)
end

# Give add_invoices an array of 1000 invoices at a time
Invoice.find_in_batches(include: :invoice_lines) 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)

Post.where(author: author)
Author.joins(:posts).where(posts: { 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))

Post.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.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

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

SELECT * FROM posts WHERE id > 10 LIMIT 20

# Original query without `unscope`
SELECT * FROM posts WHERE id > 10 ORDER BY id asc LIMIT 20

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

Post.order('id asc').merge(Post.unscope(:order))
# SELECT "posts".* FROM "posts"

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

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

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

class Post < ActiveRecord::Base
  ..
  ..
  has_many :comments, -> { order('posted_at DESC') }
end

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

SELECT * FROM posts WHERE id = 10 ORDER BY name

SELECT * FROM posts WHERE 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

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

SELECT * FROM posts WHERE `trashed` = 0

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

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

Post.none # returns an empty Relation and fires no queries.

# The visible_posts method below is expected to return a Relation.
@posts = current_user.visible_posts.where(name: params[:name])

def visible_posts
  case role
  when 'Country Manager'
    Post.where(country: country)
  when 'Reviewer'
    Post.published
  when 'Bad User'
    Post.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 :posts
end

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

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

class Guest < ActiveRecord::Base
  belongs_to :comment
end

class Tag < ActiveRecord::Base
  belongs_to :post
end

Category.joins(:posts)

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

Post.joins(:category, :comments)

SELECT posts.* FROM posts
  INNER JOIN categories ON posts.category_id = categories.id
  INNER JOIN comments ON comments.post_id = posts.id

Post.joins(comments: :guest)

SELECT posts.* FROM posts
  INNER JOIN comments ON comments.post_id = posts.id
  INNER JOIN guests ON guests.comment_id = comments.id

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

SELECT categories.* FROM categories
  INNER JOIN posts ON posts.category_id = categories.id
  INNER JOIN comments ON comments.post_id = posts.id
  INNER JOIN guests ON guests.comment_id = comments.id
  INNER JOIN tags ON tags.post_id = posts.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))

Post.includes(:category, :comments)

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

Post.includes(:comments).where("comments.visible" => true)

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

class Post < ActiveRecord::Base
  scope :published, -> { where(published: true) }
end

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

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

Post.published # => [published posts]

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

class Post < ActiveRecord::Base
  scope :created_before, ->(time) { where("created_at < ?", time) }
end

Post.created_before(Time.zone.now)

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

category.posts.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" = 'pending' AND "users"."state" = 'active'

User.where(state: 'inactive')
# SELECT "users".* FROM "users" WHERE "users"."state" = 'pending' AND "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 BY clients.created_at desc")

Client.connection.select_all("SELECT * FROM clients WHERE id = '1'")

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
Post.any?
Post.many?

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

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

# via an association
Post.first.categories.any?
Post.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(:posts).explain

EXPLAIN for: SELECT `users`.* FROM `users` INNER JOIN `posts` ON `posts`.`user_id` = `users`.`id` WHERE `users`.`id` = 1
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | users | const | PRIMARY       | PRIMARY | 4       | const |    1 |             |
|  1 | SIMPLE      | posts | ALL   | NULL          | NULL    | NULL    | NULL  |    1 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
2 rows in set (0.00 sec)

EXPLAIN for: SELECT "users".* FROM "users" INNER JOIN "posts" ON "posts"."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: (posts.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 posts  (cost=0.00..28.88 rows=8 width=4)
         Filter: (posts.user_id = 1)
(6 rows)

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

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

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