active_record_querying.md 55.6 KB
Newer Older
1
**DO NOT READ THIS FILE ON GITHUB, GUIDES ARE PUBLISHED ON http://guides.rubyonrails.org.**
X
Xavier Noria 已提交
2

3 4
Active Record Query Interface
=============================
5

6 7 8
This guide covers different ways to retrieve data from the database using Active Record.

After reading this guide, you will know:
9

10 11 12 13
* How to find records using a variety of methods and conditions.
* How to specify the order, retrieved attributes, grouping, and other properties of the found records.
* How to use eager loading to reduce the number of database queries needed for data retrieval.
* How to use dynamic finders methods.
14
* How to use method chaining to use multiple ActiveRecord methods together.
15 16 17
* How to check for the existence of particular records.
* How to perform various calculations on Active Record models.
* How to run EXPLAIN on relations.
18

19
--------------------------------------------------------------------------------
20

21
If you're used to using raw SQL to find database records, then you will generally find that there are better ways to carry out the same operations in Rails. Active Record insulates you from the need to use SQL in most cases.
22 23 24

Code examples throughout this guide will refer to one or more of the following models:

25
TIP: All of the following models use `id` as the primary key, unless specified otherwise.
26

27
```ruby
28 29 30 31 32
class Client < ActiveRecord::Base
  has_one :address
  has_many :orders
  has_and_belongs_to_many :roles
end
33
```
34

35
```ruby
36 37 38
class Address < ActiveRecord::Base
  belongs_to :client
end
39
```
40

41
```ruby
42
class Order < ActiveRecord::Base
A
Agis Anastasopoulos 已提交
43
  belongs_to :client, counter_cache: true
44
end
45
```
46

47
```ruby
48 49 50
class Role < ActiveRecord::Base
  has_and_belongs_to_many :clients
end
51
```
52

53
Active Record will perform queries on the database for you and is compatible with most database systems (MySQL, PostgreSQL and SQLite to name a few). Regardless of which database system you're using, the Active Record method format will always be the same.
54

55 56
Retrieving Objects from the Database
------------------------------------
57

58
To retrieve objects from the database, Active Record provides several finder methods. Each finder method allows you to pass arguments into it to perform certain queries on your database without writing raw SQL.
59 60

The methods are:
61

62 63
* `bind`
* `create_with`
64
* `distinct`
65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84
* `eager_load`
* `extending`
* `from`
* `group`
* `having`
* `includes`
* `joins`
* `limit`
* `lock`
* `none`
* `offset`
* `order`
* `preload`
* `readonly`
* `references`
* `reorder`
* `reverse_order`
* `select`
* `uniq`
* `where`
85

86
All of the above methods return an instance of `ActiveRecord::Relation`.
87

88
The primary operation of `Model.find(options)` can be summarized as:
89 90 91 92

* Convert the supplied options to an equivalent SQL query.
* Fire the SQL query and retrieve the corresponding results from the database.
* Instantiate the equivalent Ruby object of the appropriate model for every resulting row.
93
* Run `after_find` and then `after_initialize` callbacks, if any.
94

95
### Retrieving a Single Object
96

97
Active Record provides several different ways of retrieving a single object.
98

S
schneems 已提交
99
#### `find`
100

S
schneems 已提交
101
Using the `find` method, you can retrieve the object corresponding to the specified _primary key_ that matches any supplied options. For example:
102

103
```ruby
104 105
# Find the client with primary key (id) 10.
client = Client.find(10)
106
# => #<Client id: 10, first_name: "Ryan">
107
```
108

109
The SQL equivalent of the above is:
110

111
```sql
112
SELECT * FROM clients WHERE (clients.id = 10) LIMIT 1
113
```
114

S
schneems 已提交
115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131
The `find` method will raise an `ActiveRecord::RecordNotFound` exception if no matching record is found.

You can also use this method to query for multiple objects. Call the `find` method and pass in an array of primary keys. The return will be an array containing all of the matching records for the supplied _primary keys_. For example:

```ruby
# 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">]
```

The SQL equivalent of the above is:

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

WARNING: The `find` method will raise an `ActiveRecord::RecordNotFound` exception unless a matching record is found for **all** of the supplied primary keys.
132

133
#### `take`
134

S
schneems 已提交
135
The `take` method retrieves a record without any implicit ordering. For example:
136

137
```ruby
138 139
client = Client.take
# => #<Client id: 1, first_name: "Lifo">
140
```
141 142 143

The SQL equivalent of the above is:

144
```sql
145
SELECT * FROM clients LIMIT 1
146
```
147

S
schneems 已提交
148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166
The `take` method returns `nil` if no record is found and no exception will be raised.

You can pass in a numerical argument to the `take` method to return up to that number of results. For example

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

The SQL equivalent of the above is:

```sql
SELECT * FROM clients LIMIT 2
```

The `take!` method behaves exactly like `take`, except that it will raise `ActiveRecord::RecordNotFound` if no matching record is found.
167 168

TIP: The retrieved record may vary depending on the database engine.
169

170
#### `first`
171

172
The `first` method finds the first record ordered by the primary key. For example:
173

174
```ruby
175
client = Client.first
176
# => #<Client id: 1, first_name: "Lifo">
177
```
178

179
The SQL equivalent of the above is:
180

181
```sql
182
SELECT * FROM clients ORDER BY clients.id ASC LIMIT 1
183
```
184

185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204
The `first` method returns `nil` if no matching record is found and no exception will be raised.

You can pass in a numerical argument to the `first` method to return up to that number of results. For example

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

The SQL equivalent of the above is:

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

The `first!` method behaves exactly like `first`, except that it will raise `ActiveRecord::RecordNotFound` if no matching record is found.
205

206
#### `last`
207

S
schneems 已提交
208
The `last` method finds the last record ordered by the primary key. For example:
209

210
```ruby
211
client = Client.last
212
# => #<Client id: 221, first_name: "Russel">
213
```
214

215
The SQL equivalent of the above is:
216

217
```sql
218
SELECT * FROM clients ORDER BY clients.id DESC LIMIT 1
219
```
220

S
schneems 已提交
221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240
The `last` method returns `nil` if no matching record is found and no exception will be raised.

You can pass in a numerical argument to the `last` method to return up to that number of results. For example

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

The SQL equivalent of the above is:

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

The `last!` method behaves exactly like `last`, except that it will raise `ActiveRecord::RecordNotFound` if no matching record is found.
241

242
#### `find_by`
243

244
The `find_by` method finds the first record matching some conditions. For example:
245

246
```ruby
247 248 249 250 251
Client.find_by first_name: 'Lifo'
# => #<Client id: 1, first_name: "Lifo">

Client.find_by first_name: 'Jon'
# => nil
252
```
253 254 255

It is equivalent to writing:

256
```ruby
257
Client.where(first_name: 'Lifo').take
258
```
259

260 261 262 263 264 265
The SQL equivalent of the above is:

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

266 267 268 269 270 271 272 273 274 275 276 277 278
The `find_by!` method behaves exactly like `find_by`, except that it will raise `ActiveRecord::RecordNotFound` if no matching record is found. For example:

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

This is equivalent to writing:

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

279
### Retrieving Multiple Objects in Batches
280

281
We often need to iterate over a large set of records, as when we send a newsletter to a large set of users, or when we export data.
282

283
This may appear straightforward:
284

285
```ruby
286
# This is very inefficient when the users table has thousands of rows.
P
Pratik Naik 已提交
287
User.all.each do |user|
288
  NewsMailer.weekly(user).deliver_now
289
end
290
```
291

292
But this approach becomes increasingly impractical as the table size increases, since `User.all.each` instructs Active Record to fetch _the entire table_ in a single pass, build a model object per row, and then keep the entire array of model objects in memory. Indeed, if we have a large number of records, the entire collection may exceed the amount of memory available.
293

294
Rails provides two methods that address this problem by dividing records into memory-friendly batches for processing. The first method, `find_each`, retrieves a batch of records and then yields _each_ record to the block individually as a model. The second method, `find_in_batches`, retrieves a batch of records and then yields _the entire batch_ to the block as an array of models.
295

296
TIP: The `find_each` and `find_in_batches` methods are intended for use in the batch processing of a large number of records that wouldn't fit in memory all at once. If you just need to loop over a thousand records the regular find methods are the preferred option.
297

298
#### `find_each`
299

300
The `find_each` method retrieves a batch of records and then yields _each_ record to the block individually as a model. In the following example, `find_each` will retrieve 1000 records (the current default for both `find_each` and `find_in_batches`) and then yield each record individually to the block as a model. This process is repeated until all of the records have been processed:
301

302
```ruby
303
User.find_each do |user|
304
  NewsMailer.weekly(user).deliver_now
305 306 307 308 309 310 311
end
```

To add conditions to a `find_each` operation you can chain other Active Record methods such as `where`:

```ruby
User.where(weekly_subscriber: true).find_each do |user|
312
  NewsMailer.weekly(user).deliver_now
313
end
314
```
315

316
##### Options for `find_each`
317

318
The `find_each` method accepts most of the options allowed by the regular `find` method, except for `:order` and `:limit`, which are reserved for internal use by `find_each`.
319

320
Two additional options, `:batch_size` and `:start`, are available as well.
321

322
**`:batch_size`**
323

324
The `:batch_size` option allows you to specify the number of records to be retrieved in each batch, before being passed individually to the block. For example, to retrieve records in batches of 5000:
325

326
```ruby
A
Agis Anastasopoulos 已提交
327
User.find_each(batch_size: 5000) do |user|
328
  NewsMailer.weekly(user).deliver_now
329
end
330
```
331

332
**`:start`**
333

334
By default, records are fetched in ascending order of the primary key, which must be an integer. The `:start` option allows you to configure the first ID of the sequence whenever the lowest ID is not the one you need. This would be useful, for example, if you wanted to resume an interrupted batch process, provided you saved the last processed ID as a checkpoint.
335

336
For example, to send newsletters only to users with the primary key starting from 2000, and to retrieve them in batches of 5000:
337

338
```ruby
A
Agis Anastasopoulos 已提交
339
User.find_each(start: 2000, batch_size: 5000) do |user|
340
  NewsMailer.weekly(user).deliver_now
341
end
342
```
343

344
Another example would be if you wanted multiple workers handling the same processing queue. You could have each worker handle 10000 records by setting the appropriate `:start` option on each worker.
345

346
#### `find_in_batches`
347

348
The `find_in_batches` method is similar to `find_each`, since both retrieve batches of records. The difference is that `find_in_batches` yields _batches_ to the block as an array of models, instead of individually. The following example will yield to the supplied block an array of up to 1000 invoices at a time, with the final block containing any remaining invoices:
349

350
```ruby
351
# Give add_invoices an array of 1000 invoices at a time
352
Invoice.find_in_batches do |invoices|
353 354
  export.add_invoices(invoices)
end
355
```
356

357
##### Options for `find_in_batches`
358

359
The `find_in_batches` method accepts the same `:batch_size` and `:start` options as `find_each`.
360

361 362
Conditions
----------
363

364
The `where` method allows you to specify conditions to limit the records returned, representing the `WHERE`-part of the SQL statement. Conditions can either be specified as a string, array, or hash.
365

366
### Pure String Conditions
367

368
If you'd like to add conditions to your find, you could just specify them in there, just like `Client.where("orders_count = '2'")`. This will find all clients where the `orders_count` field's value is 2.
369

370
WARNING: Building your own conditions as pure strings can leave you vulnerable to SQL injection exploits. For example, `Client.where("first_name LIKE '%#{params[:first_name]}%'")` is not safe. See the next section for the preferred way to handle conditions using an array.
371

372
### Array Conditions
373

374
Now what if that number could vary, say as an argument from somewhere? The find would then take the form:
P
Pratik Naik 已提交
375

376
```ruby
P
Pratik Naik 已提交
377
Client.where("orders_count = ?", params[:orders])
378
```
P
Pratik Naik 已提交
379

380
Active Record will go through the first element in the conditions value and any additional elements will replace the question marks `(?)` in the first element.
P
Pratik Naik 已提交
381

382
If you want to specify multiple conditions:
P
Pratik Naik 已提交
383

384
```ruby
P
Pratik Naik 已提交
385
Client.where("orders_count = ? AND locked = ?", params[:orders], false)
386
```
P
Pratik Naik 已提交
387

388
In this example, the first question mark will be replaced with the value in `params[:orders]` and the second will be replaced with the SQL representation of `false`, which depends on the adapter.
389

390
This code is highly preferable:
391

392
```ruby
P
Pratik Naik 已提交
393
Client.where("orders_count = ?", params[:orders])
394
```
395

396
to this code:
397

398
```ruby
399
Client.where("orders_count = #{params[:orders]}")
400
```
401

402
because of argument safety. Putting the variable directly into the conditions string will pass the variable to the database **as-is**. This means that it will be an unescaped variable directly from a user who may have malicious intent. If you do this, you put your entire database at risk because once a user finds out they can exploit your database they can do just about anything to it. Never ever put your arguments directly inside the conditions string.
403

404
TIP: For more information on the dangers of SQL injection, see the [Ruby on Rails Security Guide](security.html#sql-injection).
405

406
#### Placeholder Conditions
P
Pratik Naik 已提交
407

408
Similar to the `(?)` replacement style of params, you can also specify keys/values hash in your array conditions:
P
Pratik Naik 已提交
409

410
```ruby
411
Client.where("created_at >= :start_date AND created_at <= :end_date",
A
Agis Anastasopoulos 已提交
412
  {start_date: params[:start_date], end_date: params[:end_date]})
413
```
P
Pratik Naik 已提交
414 415 416

This makes for clearer readability if you have a large number of variable conditions.

417
### Hash Conditions
418

419
Active Record also allows you to pass in hash conditions which can increase the readability of your conditions syntax. With hash conditions, you pass in a hash with keys of the fields you want conditionalised and the values of how you want to conditionalise them:
420

P
Pratik Naik 已提交
421 422
NOTE: Only equality, range and subset checking are possible with Hash conditions.

423
#### Equality Conditions
P
Pratik Naik 已提交
424

425
```ruby
A
Agis Anastasopoulos 已提交
426
Client.where(locked: true)
427
```
428

429
The field name can also be a string:
430

431
```ruby
432
Client.where('locked' => true)
433
```
434

S
Steve Klabnik 已提交
435
In the case of a belongs_to relationship, an association key can be used to specify the model if an Active Record object is used as the value. This method works with polymorphic relationships as well.
436

437
```ruby
438 439
Article.where(author: author)
Author.joins(:articles).where(articles: { author: author })
440
```
441

A
Agis Anastasopoulos 已提交
442
NOTE: The values cannot be symbols. For example, you cannot do `Client.where(status: :active)`.
443

444
#### Range Conditions
P
Pratik Naik 已提交
445

446
```ruby
A
Agis Anastasopoulos 已提交
447
Client.where(created_at: (Time.now.midnight - 1.day)..Time.now.midnight)
448
```
449

450
This will find all clients created yesterday by using a `BETWEEN` SQL statement:
451

452
```sql
453
SELECT * FROM clients WHERE (clients.created_at BETWEEN '2008-12-21 00:00:00' AND '2008-12-22 00:00:00')
454
```
455

456
This demonstrates a shorter syntax for the examples in [Array Conditions](#array-conditions)
457

458
#### Subset Conditions
P
Pratik Naik 已提交
459

460
If you want to find records using the `IN` expression you can pass an array to the conditions hash:
461

462
```ruby
A
Agis Anastasopoulos 已提交
463
Client.where(orders_count: [1,3,5])
464
```
465

P
Pratik Naik 已提交
466
This code will generate SQL like this:
467

468
```sql
469
SELECT * FROM clients WHERE (clients.orders_count IN (1,3,5))
470
```
471

472
### NOT Conditions
473

474
`NOT` SQL queries can be built by `where.not`.
475 476

```ruby
477
Article.where.not(author: author)
478 479
```

480
In other words, this query can be generated by calling `where` with no argument, then immediately chain with `not` passing `where` conditions.
481

482 483
Ordering
--------
484

485
To retrieve records from the database in a specific order, you can use the `order` method.
486

487
For example, if you're getting a set of records and want to order them in ascending order by the `created_at` field in your table:
488

489
```ruby
490 491
Client.order(:created_at)
# OR
492
Client.order("created_at")
493
```
494

495
You could specify `ASC` or `DESC` as well:
496

497
```ruby
498 499 500 501
Client.order(created_at: :desc)
# OR
Client.order(created_at: :asc)
# OR
502
Client.order("created_at DESC")
503
# OR
504
Client.order("created_at ASC")
505
```
506 507 508

Or ordering by multiple fields:

509
```ruby
510 511 512 513
Client.order(orders_count: :asc, created_at: :desc)
# OR
Client.order(:orders_count, created_at: :desc)
# OR
514
Client.order("orders_count ASC, created_at DESC")
515 516
# OR
Client.order("orders_count ASC", "created_at DESC")
517
```
518

519
If you want to call `order` multiple times e.g. in different context, new order will append previous one
520

521
```ruby
522
Client.order("orders_count ASC").order("created_at DESC")
523
# SELECT * FROM clients ORDER BY orders_count ASC, created_at DESC
524
```
525

526 527
Selecting Specific Fields
-------------------------
528

529
By default, `Model.find` selects all the fields from the result set using `select *`.
530

531
To select only a subset of fields from the result set, you can specify the subset via the `select` method.
532

533
For example, to select only `viewable_by` and `locked` columns:
534

535
```ruby
536
Client.select("viewable_by, locked")
537
```
538 539 540

The SQL query used by this find call will be somewhat like:

541
```sql
542
SELECT viewable_by, locked FROM clients
543
```
544 545 546

Be careful because this also means you're initializing a model object with only the fields that you've selected. If you attempt to access a field that is not in the initialized record you'll receive:

P
Prem Sichanugrist 已提交
547
```bash
548
ActiveModel::MissingAttributeError: missing attribute: <attribute>
549
```
550

551
Where `<attribute>` is the attribute you asked for. The `id` method will not raise the `ActiveRecord::MissingAttributeError`, so just be careful when working with associations because they need the `id` method to function properly.
552

553
If you would like to only grab a single record per unique value in a certain field, you can use `distinct`:
554

555
```ruby
556
Client.select(:name).distinct
557
```
558 559 560

This would generate SQL like:

561
```sql
562
SELECT DISTINCT name FROM clients
563
```
564 565 566

You can also remove the uniqueness constraint:

567
```ruby
568
query = Client.select(:name).distinct
569 570
# => Returns unique names

571
query.distinct(false)
572
# => Returns all names, even if there are duplicates
573
```
574

575 576
Limit and Offset
----------------
577

578
To apply `LIMIT` to the SQL fired by the `Model.find`, you can specify the `LIMIT` using `limit` and `offset` methods on the relation.
579

580
You can use `limit` to specify the number of records to be retrieved, and use `offset` to specify the number of records to skip before starting to return the records. For example
581

582
```ruby
583
Client.limit(5)
584
```
585

586
will return a maximum of 5 clients and because it specifies no offset it will return the first 5 in the table. The SQL it executes looks like this:
587

588
```sql
589
SELECT * FROM clients LIMIT 5
590
```
591

592
Adding `offset` to that
593

594
```ruby
595
Client.limit(5).offset(30)
596
```
597

598
will return instead a maximum of 5 clients beginning with the 31st. The SQL looks like:
599

600
```sql
A
Akira Matsuda 已提交
601
SELECT * FROM clients LIMIT 5 OFFSET 30
602
```
603

604 605
Group
-----
606

607
To apply a `GROUP BY` clause to the SQL fired by the finder, you can specify the `group` method on the find.
608 609

For example, if you want to find a collection of the dates orders were created on:
610

611
```ruby
A
Akira Matsuda 已提交
612
Order.select("date(created_at) as ordered_date, sum(price) as total_price").group("date(created_at)")
613
```
614

615
And this will give you a single `Order` object for each date where there are orders in the database.
616 617 618

The SQL that would be executed would be something like this:

619
```sql
620 621
SELECT date(created_at) as ordered_date, sum(price) as total_price
FROM orders
B
Bertrand Chardon 已提交
622
GROUP BY date(created_at)
623
```
624

625 626 627 628 629 630
### Total of grouped items

To get the total of grouped items on a single query call `count` after the `group`.

```ruby
Order.group(:status).count
631
# => { 'awaiting_approval' => 7, 'paid' => 12 }
632 633 634 635 636 637 638 639 640 641
```

The SQL that would be executed would be something like this:

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

642 643
Having
------
644

645
SQL uses the `HAVING` clause to specify conditions on the `GROUP BY` fields. You can add the `HAVING` clause to the SQL fired by the `Model.find` by adding the `:having` option to the find.
646

647
For example:
648

649
```ruby
650 651
Order.select("date(created_at) as ordered_date, sum(price) as total_price").
  group("date(created_at)").having("sum(price) > ?", 100)
652
```
653

654 655
The SQL that would be executed would be something like this:

656
```sql
657 658 659
SELECT date(created_at) as ordered_date, sum(price) as total_price
FROM orders
GROUP BY date(created_at)
B
Bertrand Chardon 已提交
660
HAVING sum(price) > 100
661
```
662

A
Akira Matsuda 已提交
663
This will return single order objects for each day, but only those that are ordered more than $100 in a day.
664

665 666
Overriding Conditions
---------------------
667

668
### `unscope`
669

670
You can specify certain conditions to be removed using the `unscope` method. For example:
671

672
```ruby
673
Article.where('id > 10').limit(20).order('id asc').unscope(:order)
674
```
675 676 677

The SQL that would be executed:

678
```sql
679
SELECT * FROM articles WHERE id > 10 LIMIT 20
680

681
# Original query without `unscope`
682
SELECT * FROM articles WHERE id > 10 ORDER BY id asc LIMIT 20
683

684
```
685

686
You can also unscope specific `where` clauses. For example:
687 688

```ruby
689 690
Article.where(id: 10, trashed: false).unscope(where: :id)
# SELECT "articles".* FROM "articles" WHERE trashed = 0
691 692
```

J
Jon Leighton 已提交
693 694
A relation which has used `unscope` will affect any relation it is
merged in to:
695 696

```ruby
697 698
Article.order('id asc').merge(Article.unscope(:order))
# SELECT "articles".* FROM "articles"
699 700
```

701
### `only`
702

703
You can also override conditions using the `only` method. For example:
704

705
```ruby
706
Article.where('id > 10').limit(20).order('id desc').only(:order, :where)
707
```
708 709 710

The SQL that would be executed:

711
```sql
712
SELECT * FROM articles WHERE id > 10 ORDER BY id DESC
713 714

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

717
```
718

719
### `reorder`
720

721
The `reorder` method overrides the default scope order. For example:
722

723
```ruby
724
class Article < ActiveRecord::Base
725
  has_many :comments, -> { order('posted_at DESC') }
726 727
end

728
Article.find(10).comments.reorder('name')
729
```
730 731 732

The SQL that would be executed:

733
```sql
734 735
SELECT * FROM articles WHERE id = 10
SELECT * FROM comments WHERE article_id = 10 ORDER BY name
736
```
737

738
In case the `reorder` clause is not used, the SQL executed would be:
739

740
```sql
741 742
SELECT * FROM articles WHERE id = 10
SELECT * FROM comments WHERE article_id = 10 ORDER BY posted_at DESC
743
```
744

745
### `reverse_order`
746

747
The `reverse_order` method reverses the ordering clause if specified.
748

749
```ruby
750
Client.where("orders_count > 10").order(:name).reverse_order
751
```
752 753

The SQL that would be executed:
754

755
```sql
756
SELECT * FROM clients WHERE orders_count > 10 ORDER BY name DESC
757
```
758

759
If no ordering clause is specified in the query, the `reverse_order` orders by the primary key in reverse order.
760

761
```ruby
762
Client.where("orders_count > 10").reverse_order
763
```
764 765

The SQL that would be executed:
766

767
```sql
768
SELECT * FROM clients WHERE orders_count > 10 ORDER BY clients.id DESC
769
```
770

771
This method accepts **no** arguments.
772

773 774 775 776 777
### `rewhere`

The `rewhere` method overrides an existing, named where condition. For example:

```ruby
778
Article.where(trashed: true).rewhere(trashed: false)
779 780 781 782 783
```

The SQL that would be executed:

```sql
784
SELECT * FROM articles WHERE `trashed` = 0
785 786 787 788 789
```

In case the `rewhere` clause is not used,

```ruby
790
Article.where(trashed: true).where(trashed: false)
791 792 793 794 795
```

the SQL executed would be:

```sql
796
SELECT * FROM articles WHERE `trashed` = 1 AND `trashed` = 0
797 798
```

799 800
Null Relation
-------------
801

802
The `none` method returns a chainable relation with no records. Any subsequent conditions chained to the returned relation will continue generating empty relations. This is useful in scenarios where you need a chainable response to a method or a scope that could return zero results.
803

804
```ruby
805
Article.none # returns an empty Relation and fires no queries.
806
```
807

808
```ruby
809 810
# The visible_articles method below is expected to return a Relation.
@articles = current_user.visible_articles.where(name: params[:name])
811

812
def visible_articles
813 814
  case role
  when 'Country Manager'
815
    Article.where(country: country)
816
  when 'Reviewer'
817
    Article.published
818
  when 'Bad User'
819
    Article.none # => returning [] or nil breaks the caller code in this case
820 821
  end
end
822
```
823

824 825
Readonly Objects
----------------
826

827
Active Record provides `readonly` method on a relation to explicitly disallow modification of any of the returned objects. Any attempt to alter a readonly record will not succeed, raising an `ActiveRecord::ReadOnlyRecord` exception.
828

829
```ruby
P
Pratik Naik 已提交
830 831
client = Client.readonly.first
client.visits += 1
832
client.save
833
```
834

835
As `client` is explicitly set to be a readonly object, the above code will raise an `ActiveRecord::ReadOnlyRecord` exception when calling `client.save` with an updated value of _visits_.
P
Pratik Naik 已提交
836

837 838
Locking Records for Update
--------------------------
839

840 841 842
Locking is helpful for preventing race conditions when updating records in the database and ensuring atomic updates.

Active Record provides two locking mechanisms:
843 844 845 846

* Optimistic Locking
* Pessimistic Locking

847
### Optimistic Locking
848

849
Optimistic locking allows multiple users to access the same record for edits, and assumes a minimum of conflicts with the data. It does this by checking whether another process has made changes to a record since it was opened. An `ActiveRecord::StaleObjectError` exception is thrown if that has occurred and the update is ignored.
850

851
**Optimistic locking column**
852

853
In order to use optimistic locking, the table needs to have a column called `lock_version` of type integer. Each time the record is updated, Active Record increments the `lock_version` column. If an update request is made with a lower value in the `lock_version` field than is currently in the `lock_version` column in the database, the update request will fail with an `ActiveRecord::StaleObjectError`. Example:
854

855
```ruby
856 857 858
c1 = Client.find(1)
c2 = Client.find(1)

859
c1.first_name = "Michael"
860 861 862
c1.save

c2.name = "should fail"
M
Michael Hutchinson 已提交
863
c2.save # Raises an ActiveRecord::StaleObjectError
864
```
865 866 867

You're then responsible for dealing with the conflict by rescuing the exception and either rolling back, merging, or otherwise apply the business logic needed to resolve the conflict.

868
This behavior can be turned off by setting `ActiveRecord::Base.lock_optimistically = false`.
869

870
To override the name of the `lock_version` column, `ActiveRecord::Base` provides a class attribute called `locking_column`:
871

872
```ruby
873
class Client < ActiveRecord::Base
874
  self.locking_column = :lock_client_column
875
end
876
```
877

878
### Pessimistic Locking
879

880
Pessimistic locking uses a locking mechanism provided by the underlying database. Using `lock` when building a relation obtains an exclusive lock on the selected rows. Relations using `lock` are usually wrapped inside a transaction for preventing deadlock conditions.
881 882

For example:
883

884
```ruby
885
Item.transaction do
P
Pratik Naik 已提交
886
  i = Item.lock.first
887 888
  i.name = 'Jones'
  i.save
889
end
890
```
891

892 893
The above session produces the following SQL for a MySQL backend:

894
```sql
895 896 897 898
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
899
```
900

901
You can also pass raw SQL to the `lock` method for allowing different types of locks. For example, MySQL has an expression called `LOCK IN SHARE MODE` where you can lock a record but still allow other queries to read it. To specify this expression just pass it in as the lock option:
902

903
```ruby
904
Item.transaction do
P
Pratik Naik 已提交
905
  i = Item.lock("LOCK IN SHARE MODE").find(1)
906 907
  i.increment!(:views)
end
908
```
909

910 911
If you already have an instance of your model, you can start a transaction and acquire the lock in one go using the following code:

912
```ruby
913 914 915 916 917 918
item = Item.first
item.with_lock do
  # This block is called within a transaction,
  # item is already locked.
  item.increment!(:views)
end
919
```
920

921 922
Joining Tables
--------------
923

924
Active Record provides a finder method called `joins` for specifying `JOIN` clauses on the resulting SQL. There are multiple ways to use the `joins` method.
925

926
### Using a String SQL Fragment
927

928
You can just supply the raw SQL specifying the `JOIN` clause to `joins`:
929

930
```ruby
P
Pratik Naik 已提交
931
Client.joins('LEFT OUTER JOIN addresses ON addresses.client_id = clients.id')
932
```
933 934 935

This will result in the following SQL:

936
```sql
P
Pratik Naik 已提交
937
SELECT clients.* FROM clients LEFT OUTER JOIN addresses ON addresses.client_id = clients.id
938
```
939

940
### Using Array/Hash of Named Associations
941

942
WARNING: This method only works with `INNER JOIN`.
943

944
Active Record lets you use the names of the [associations](association_basics.html) defined on the model as a shortcut for specifying `JOIN` clauses for those associations when using the `joins` method.
945

946
For example, consider the following `Category`, `Article`, `Comment`, `Guest` and `Tag` models:
947

948
```ruby
949
class Category < ActiveRecord::Base
950
  has_many :articles
951 952
end

953
class Article < ActiveRecord::Base
954 955 956 957 958
  belongs_to :category
  has_many :comments
  has_many :tags
end

959
class Comment < ActiveRecord::Base
960
  belongs_to :article
961 962 963 964 965 966
  has_one :guest
end

class Guest < ActiveRecord::Base
  belongs_to :comment
end
967 968

class Tag < ActiveRecord::Base
969
  belongs_to :article
970
end
971
```
972

973
Now all of the following will produce the expected join queries using `INNER JOIN`:
974

975
#### Joining a Single Association
976

977
```ruby
978
Category.joins(:articles)
979
```
980 981 982

This produces:

983
```sql
984
SELECT categories.* FROM categories
985
  INNER JOIN articles ON articles.category_id = categories.id
986
```
987

988
Or, in English: "return a Category object for all categories with articles". Note that you will see duplicate categories if more than one article has the same category. If you want unique categories, you can use `Category.joins(:articles).uniq`.
989

990
#### Joining Multiple Associations
991

992
```ruby
993
Article.joins(:category, :comments)
994
```
995

996
This produces:
997

998
```sql
999 1000 1001
SELECT articles.* FROM articles
  INNER JOIN categories ON articles.category_id = categories.id
  INNER JOIN comments ON comments.article_id = articles.id
1002
```
1003

1004
Or, in English: "return all articles that have a category and at least one comment". Note again that articles with multiple comments will show up multiple times.
1005

1006
#### Joining Nested Associations (Single Level)
1007

1008
```ruby
1009
Article.joins(comments: :guest)
1010
```
1011

1012 1013
This produces:

1014
```sql
1015 1016
SELECT articles.* FROM articles
  INNER JOIN comments ON comments.article_id = articles.id
1017
  INNER JOIN guests ON guests.comment_id = comments.id
1018
```
1019

1020
Or, in English: "return all articles that have a comment made by a guest."
1021

1022
#### Joining Nested Associations (Multiple Level)
1023

1024
```ruby
1025
Category.joins(articles: [{ comments: :guest }, :tags])
1026
```
1027

1028 1029
This produces:

1030
```sql
1031
SELECT categories.* FROM categories
1032 1033
  INNER JOIN articles ON articles.category_id = categories.id
  INNER JOIN comments ON comments.article_id = articles.id
1034
  INNER JOIN guests ON guests.comment_id = comments.id
1035
  INNER JOIN tags ON tags.article_id = articles.id
1036
```
1037

1038
### Specifying Conditions on the Joined Tables
1039

1040
You can specify conditions on the joined tables using the regular [Array](#array-conditions) and [String](#pure-string-conditions) conditions. [Hash conditions](#hash-conditions) provides a special syntax for specifying conditions for the joined tables:
1041

1042
```ruby
1043
time_range = (Time.now.midnight - 1.day)..Time.now.midnight
1044
Client.joins(:orders).where('orders.created_at' => time_range)
1045
```
1046

1047
An alternative and cleaner syntax is to nest the hash conditions:
1048

1049
```ruby
1050
time_range = (Time.now.midnight - 1.day)..Time.now.midnight
1051
Client.joins(:orders).where(orders: { created_at: time_range })
1052
```
1053

1054
This will find all clients who have orders that were created yesterday, again using a `BETWEEN` SQL expression.
1055

1056 1057
Eager Loading Associations
--------------------------
1058

1059
Eager loading is the mechanism for loading the associated records of the objects returned by `Model.find` using as few queries as possible.
1060

1061
**N + 1 queries problem**
1062 1063 1064

Consider the following code, which finds 10 clients and prints their postcodes:

1065
```ruby
V
Vijay Dev 已提交
1066
clients = Client.limit(10)
1067 1068 1069 1070

clients.each do |client|
  puts client.address.postcode
end
1071
```
1072

1073
This code looks fine at the first sight. But the problem lies within the total number of queries executed. The above code executes 1 (to find 10 clients) + 10 (one per each client to load the address) = **11** queries in total.
1074

1075
**Solution to N + 1 queries problem**
1076

1077
Active Record lets you specify in advance all the associations that are going to be loaded. This is possible by specifying the `includes` method of the `Model.find` call. With `includes`, Active Record ensures that all of the specified associations are loaded using the minimum possible number of queries.
1078

1079
Revisiting the above case, we could rewrite `Client.limit(10)` to use eager load addresses:
1080

1081
```ruby
J
James Miller 已提交
1082
clients = Client.includes(:address).limit(10)
1083 1084 1085 1086

clients.each do |client|
  puts client.address.postcode
end
1087
```
1088

1089
The above code will execute just **2** queries, as opposed to **11** queries in the previous case:
1090

1091
```sql
1092
SELECT * FROM clients LIMIT 10
1093 1094
SELECT addresses.* FROM addresses
  WHERE (addresses.client_id IN (1,2,3,4,5,6,7,8,9,10))
1095
```
1096

1097
### Eager Loading Multiple Associations
1098

1099
Active Record lets you eager load any number of associations with a single `Model.find` call by using an array, hash, or a nested hash of array/hash with the `includes` method.
1100

1101
#### Array of Multiple Associations
1102

1103
```ruby
1104
Article.includes(:category, :comments)
1105
```
1106

1107
This loads all the articles and the associated category and comments for each article.
1108

1109
#### Nested Associations Hash
1110

1111
```ruby
1112
Category.includes(articles: [{ comments: :guest }, :tags]).find(1)
1113
```
1114

1115
This will find the category with id 1 and eager load all of the associated articles, the associated articles' tags and comments, and every comment's guest association.
1116

1117
### Specifying Conditions on Eager Loaded Associations
1118

1119
Even though Active Record lets you specify conditions on the eager loaded associations just like `joins`, the recommended way is to use [joins](#joining-tables) instead.
1120

1121
However if you must do this, you may use `where` as you would normally.
1122

1123
```ruby
1124
Article.includes(:comments).where(comments: { visible: true })
1125
```
1126

1127 1128
This would generate a query which contains a `LEFT OUTER JOIN` whereas the
`joins` method would generate one using the `INNER JOIN` function instead.
1129

1130
```ruby
1131
  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)
1132
```
1133

1134
If there was no `where` condition, this would generate the normal set of two queries.
1135

1136
NOTE: Using `where` like this will only work when you pass it a Hash. For
O
Oge Nnadi 已提交
1137
SQL-fragments you need to use `references` to force joined tables:
1138 1139 1140 1141 1142 1143 1144 1145 1146 1147 1148

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

If, in the case of this `includes` query, there were no comments for any
articles, all the articles would still be loaded. By using `joins` (an INNER
JOIN), the join conditions **must** match, otherwise no records will be
returned.


1149

1150 1151
Scopes
------
1152

1153
Scoping allows you to specify commonly-used queries which can be referenced as method calls on the association objects or models. With these scopes, you can use every method previously covered such as `where`, `joins` and `includes`. All scope methods will return an `ActiveRecord::Relation` object which will allow for further methods (such as other scopes) to be called on it.
1154

1155
To define a simple scope, we use the `scope` method inside the class, passing the query that we'd like to run when this scope is called:
1156

1157
```ruby
1158
class Article < ActiveRecord::Base
1159
  scope :published, -> { where(published: true) }
R
Ryan Bigg 已提交
1160
end
1161
```
1162

1163
This is exactly the same as defining a class method, and which you use is a matter of personal preference:
1164

1165
```ruby
1166
class Article < ActiveRecord::Base
1167 1168 1169
  def self.published
    where(published: true)
  end
R
Ryan Bigg 已提交
1170
end
1171
```
1172 1173 1174

Scopes are also chainable within scopes:

1175
```ruby
1176
class Article < ActiveRecord::Base
A
Agis Anastasopoulos 已提交
1177
  scope :published,               -> { where(published: true) }
1178
  scope :published_and_commented, -> { published.where("comments_count > 0") }
R
Ryan Bigg 已提交
1179
end
1180
```
1181

1182
To call this `published` scope we can call it on either the class:
1183

1184
```ruby
1185
Article.published # => [published articles]
1186
```
1187

1188
Or on an association consisting of `Article` objects:
1189

1190
```ruby
R
Ryan Bigg 已提交
1191
category = Category.first
1192
category.articles.published # => [published articles belonging to this category]
1193
```
1194

1195
### Passing in arguments
1196

J
Jon Leighton 已提交
1197
Your scope can take arguments:
1198

1199
```ruby
1200
class Article < ActiveRecord::Base
1201
  scope :created_before, ->(time) { where("created_at < ?", time) }
1202
end
1203
```
1204

1205
Call the scope as if it were a class method:
1206

1207
```ruby
1208
Article.created_before(Time.zone.now)
1209
```
1210 1211 1212

However, this is just duplicating the functionality that would be provided to you by a class method.

1213
```ruby
1214
class Article < ActiveRecord::Base
1215
  def self.created_before(time)
1216 1217 1218
    where("created_at < ?", time)
  end
end
1219
```
1220

1221 1222
Using a class method is the preferred way to accept arguments for scopes. These methods will still be accessible on the association objects:

1223
```ruby
1224
category.articles.created_before(time)
1225
```
1226

1227 1228 1229 1230 1231 1232 1233 1234 1235 1236 1237 1238 1239 1240 1241 1242 1243 1244 1245 1246 1247 1248 1249 1250 1251 1252 1253 1254 1255
### Applying a default scope

If we wish for a scope to be applied across all queries to the model we can use the
`default_scope` method within the model itself.

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

When queries are executed on this model, the SQL query will now look something like
this:

```sql
SELECT * FROM clients WHERE removed_at IS NULL
```

If you need to do more complex things with a default scope, you can alternatively
define it as a class method:

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

1256 1257 1258 1259 1260 1261 1262
### Merging of scopes

Just like `where` clauses scopes are merged using `AND` conditions.

```ruby
class User < ActiveRecord::Base
  scope :active, -> { where state: 'active' }
1263
  scope :inactive, -> { where state: 'inactive' }
1264 1265 1266
end

User.active.inactive
1267
# SELECT "users".* FROM "users" WHERE "users"."state" = 'active' AND "users"."state" = 'inactive'
1268 1269 1270
```

We can mix and match `scope` and `where` conditions and the final sql
R
Rafael Mendonça França 已提交
1271
will have all conditions joined with `AND`.
1272 1273 1274

```ruby
User.active.where(state: 'finished')
1275
# SELECT "users".* FROM "users" WHERE "users"."state" = 'active' AND "users"."state" = 'finished'
1276 1277 1278
```

If we do want the `last where clause` to win then `Relation#merge` can
R
Rafael Mendonça França 已提交
1279
be used.
1280 1281 1282

```ruby
User.active.merge(User.inactive)
1283
# SELECT "users".* FROM "users" WHERE "users"."state" = 'inactive'
1284 1285
```

1286
One important caveat is that `default_scope` will be prepended in
1287 1288 1289 1290
`scope` and `where` conditions.

```ruby
class User < ActiveRecord::Base
1291
  default_scope { where state: 'pending' }
1292
  scope :active, -> { where state: 'active' }
1293
  scope :inactive, -> { where state: 'inactive' }
1294 1295 1296
end

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

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

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

1306
As you can see above the `default_scope` is being merged in both
V
Vijay Dev 已提交
1307
`scope` and `where` conditions.
1308

1309
### Removing All Scoping
1310

1311 1312
If we wish to remove scoping for any reason we can use the `unscoped` method. This is
especially useful if a `default_scope` is specified in the model and should not be
1313
applied for this particular query.
1314

1315
```ruby
1316
Client.unscoped.load
1317
```
1318 1319 1320

This method removes all scoping and will do a normal query on the table.

1321 1322
Note that chaining `unscoped` with a `scope` does not work. In these cases, it is
recommended that you use the block form of `unscoped`:
1323

1324
```ruby
1325
Client.unscoped {
V
Vipul A M 已提交
1326
  Client.created_before(Time.zone.now)
1327
}
1328
```
1329

1330 1331
Dynamic Finders
---------------
1332

1333
For every field (also known as an attribute) you define in your table, Active Record provides a finder method. If you have a field called `first_name` on your `Client` model for example, you get `find_by_first_name` for free from Active Record. If you have a `locked` field on the `Client` model, you also get `find_by_locked` and methods.
1334

1335
You can specify an exclamation point (`!`) on the end of the dynamic finders to get them to raise an `ActiveRecord::RecordNotFound` error if they do not return any records, like `Client.find_by_name!("Ryan")`
1336

1337
If you want to find both by name and locked, you can chain these finders together by simply typing "`and`" between the fields. For example, `Client.find_by_first_name_and_locked("Ryan", true)`.
1338

1339 1340 1341
Understanding The Method Chaining
---------------------------------

1342 1343
The Active Record pattern implements [Method Chaining](http://en.wikipedia.org/wiki/Method_chaining),
which allow us to use multiple Active Record methods together in a simple and straightforward way.
1344

R
Robin Dupret 已提交
1345 1346 1347 1348
You can chain methods in a statement when the previous method called returns an
`ActiveRecord::Relation`, like `all`, `where`, and `joins`. Methods that return
a single object (see [Retrieving a Single Object Section](#retrieving-a-single-object))
have to be at the end of the statement.
1349

R
Robin Dupret 已提交
1350 1351 1352
There are some examples below. This guide won't cover all the possibilities, just a few as examples.
When an Active Record method is called, the query is not immediately generated and sent to the database,
this just happens when the data is actually needed. So each example below generates a single query.
1353 1354 1355 1356 1357 1358 1359

### Retrieving filtered data from multiple tables

```ruby
Person
  .select('people.id, people.name, comments.text')
  .joins(:comments)
1360 1361 1362 1363 1364 1365 1366 1367 1368 1369 1370
  .where('comments.created_at > ?', 1.week.ago)
```

The result should be something like this:

```sql
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'
1371 1372 1373 1374 1375 1376 1377 1378 1379 1380 1381
```

### Retrieving specific data from multiple tables

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

1382 1383 1384 1385 1386 1387 1388 1389 1390 1391 1392
The above should generate:

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

1393 1394
NOTE: Remember that, if `find_by` returns more than one registry, it will take
just the first and ignore the others. Note the `LIMIT 1` statement above.
1395

1396
Find or Build a New Object
1397
--------------------------
1398

1399
It's common that you need to find a record or create it if it doesn't exist. You can do that with the `find_or_create_by` and `find_or_create_by!` methods.
1400

1401
### `find_or_create_by`
1402

1403
The `find_or_create_by` method checks whether a record with the attributes exists. If it doesn't, then `create` is called. Let's see an example.
1404

1405
Suppose you want to find a client named 'Andy', and if there's none, create one. You can do so by running:
1406

1407
```ruby
1408 1409
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">
1410
```
1411 1412

The SQL generated by this method looks like this:
1413

1414
```sql
1415
SELECT * FROM clients WHERE (clients.first_name = 'Andy') LIMIT 1
1416
BEGIN
1417
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')
1418
COMMIT
1419
```
1420

1421
`find_or_create_by` returns either the record that already exists or the new record. In our case, we didn't already have a client named Andy so the record is created and returned.
1422

1423
The new record might not be saved to the database; that depends on whether validations passed or not (just like `create`).
1424

1425
Suppose we want to set the 'locked' attribute to `false` if we're
1426 1427 1428
creating a new record, but we don't want to include it in the query. So
we want to find the client named "Andy", or if that client doesn't
exist, create a client named "Andy" which is not locked.
1429

1430
We can achieve this in two ways. The first is to use `create_with`:
1431 1432 1433 1434 1435 1436

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

The second way is using a block:
1437

1438
```ruby
1439 1440 1441 1442 1443 1444 1445 1446
Client.find_or_create_by(first_name: 'Andy') do |c|
  c.locked = false
end
```

The block will only be executed if the client is being created. The
second time we run this code, the block will be ignored.

1447
### `find_or_create_by!`
1448 1449

You can also use `find_or_create_by!` to raise an exception if the new record is invalid. Validations are not covered on this guide, but let's assume for a moment that you temporarily add
1450

1451
```ruby
A
Agis Anastasopoulos 已提交
1452
validates :orders_count, presence: true
1453
```
1454

1455
to your `Client` model. If you try to create a new `Client` without passing an `orders_count`, the record will be invalid and an exception will be raised:
1456

1457
```ruby
1458
Client.find_or_create_by!(first_name: 'Andy')
1459
# => ActiveRecord::RecordInvalid: Validation failed: Orders count can't be blank
1460
```
1461

1462
### `find_or_initialize_by`
1463

1464 1465 1466 1467 1468
The `find_or_initialize_by` method will work just like
`find_or_create_by` but it will call `new` instead of `create`. This
means that a new model instance will be created in memory but won't be
saved to the database. Continuing with the `find_or_create_by` example, we
now want the client named 'Nick':
1469

1470
```ruby
1471 1472
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">
1473 1474

nick.persisted?
1475
# => false
1476 1477

nick.new_record?
1478
# => true
1479
```
1480 1481 1482

Because the object is not yet stored in the database, the SQL generated looks like this:

1483
```sql
1484
SELECT * FROM clients WHERE (clients.first_name = 'Nick') LIMIT 1
1485
```
1486

1487
When you want to save it to the database, just call `save`:
1488

1489
```ruby
1490
nick.save
1491
# => true
1492
```
1493

1494 1495
Finding by SQL
--------------
1496

1497
If you'd like to use your own SQL to find records in a table you can use `find_by_sql`. The `find_by_sql` method will return an array of objects even if the underlying query returns just a single record. For example you could run this query:
1498

1499
```ruby
1500 1501
Client.find_by_sql("SELECT * FROM clients
  INNER JOIN orders ON clients.id = orders.client_id
1502
  ORDER BY clients.created_at desc")
1503 1504 1505 1506 1507
# =>  [
  #<Client id: 1, first_name: "Lucas" >,
  #<Client id: 2, first_name: "Jan" >,
  # ...
]
1508
```
1509

1510
`find_by_sql` provides you with a simple way of making custom calls to the database and retrieving instantiated objects.
1511

1512
### `select_all`
1513

1514
`find_by_sql` has a close relative called `connection#select_all`. `select_all` will retrieve objects from the database using custom SQL just like `find_by_sql` but will not instantiate them. Instead, you will get an array of hashes where each hash indicates a record.
1515

1516
```ruby
1517 1518 1519 1520 1521
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"}
]
1522
```
1523

1524
### `pluck`
1525

1526
`pluck` can be used to query single or multiple columns from the underlying table of a model. It accepts a list of column names as argument and returns an array of values of the specified columns with the corresponding data type.
1527

1528
```ruby
A
Agis Anastasopoulos 已提交
1529
Client.where(active: true).pluck(:id)
V
Vijay Dev 已提交
1530
# SELECT id FROM clients WHERE active = 1
1531
# => [1, 2, 3]
V
Vijay Dev 已提交
1532

1533
Client.distinct.pluck(:role)
V
Vijay Dev 已提交
1534
# SELECT DISTINCT role FROM clients
1535 1536 1537 1538 1539
# => ['admin', 'member', 'guest']

Client.pluck(:id, :name)
# SELECT clients.id, clients.name FROM clients
# => [[1, 'David'], [2, 'Jeremy'], [3, 'Jose']]
1540
```
V
Vijay Dev 已提交
1541

1542
`pluck` makes it possible to replace code like:
V
Vijay Dev 已提交
1543

1544
```ruby
V
Vijay Dev 已提交
1545
Client.select(:id).map { |c| c.id }
1546
# or
1547 1548
Client.select(:id).map(&:id)
# or
1549
Client.select(:id, :name).map { |c| [c.id, c.name] }
1550
```
V
Vijay Dev 已提交
1551

1552
with:
V
Vijay Dev 已提交
1553

1554
```ruby
V
Vijay Dev 已提交
1555
Client.pluck(:id)
1556 1557
# or
Client.pluck(:id, :name)
1558
```
1559

1560 1561 1562 1563 1564 1565 1566 1567 1568 1569 1570 1571 1572 1573 1574 1575 1576 1577 1578 1579 1580 1581 1582 1583 1584 1585 1586 1587 1588 1589 1590
Unlike `select`, `pluck` directly converts a database result into a Ruby `Array`,
without constructing `ActiveRecord` objects. This can mean better performance for
a large or often-running query. However, any model method overrides will
not be available. For example:

```ruby
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"]
```

Furthermore, unlike `select` and other `Relation` scopes, `pluck` triggers an immediate
query, and thus cannot be chained with any further scopes, although it can work with
scopes already constructed earlier:

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

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

1591
### `ids`
1592

1593
`ids` can be used to pluck all the IDs for the relation using the table's primary key.
1594

1595
```ruby
1596 1597
Person.ids
# SELECT id FROM people
1598
```
1599

1600
```ruby
1601 1602 1603 1604 1605 1606
class Person < ActiveRecord::Base
  self.primary_key = "person_id"
end

Person.ids
# SELECT person_id FROM people
1607
```
1608

1609 1610
Existence of Objects
--------------------
1611

1612 1613 1614
If you simply want to check for the existence of the object there's a method called `exists?`.
This method will query the database using the same query as `find`, but instead of returning an
object or collection of objects it will return either `true` or `false`.
1615

1616
```ruby
1617
Client.exists?(1)
1618
```
1619

1620 1621
The `exists?` method also takes multiple values, but the catch is that it will return `true` if any
one of those records exists.
1622

1623
```ruby
1624
Client.exists?(id: [1,2,3])
1625
# or
1626
Client.exists?(name: ['John', 'Sergei'])
1627
```
1628

1629
It's even possible to use `exists?` without any arguments on a model or a relation.
1630

1631
```ruby
A
Agis Anastasopoulos 已提交
1632
Client.where(first_name: 'Ryan').exists?
1633
```
1634

1635 1636
The above returns `true` if there is at least one client with the `first_name` 'Ryan' and `false`
otherwise.
P
Pratik Naik 已提交
1637

1638
```ruby
P
Pratik Naik 已提交
1639
Client.exists?
1640
```
P
Pratik Naik 已提交
1641

1642
The above returns `false` if the `clients` table is empty and `true` otherwise.
P
Pratik Naik 已提交
1643

1644
You can also use `any?` and `many?` to check for existence on a model or relation.
1645

1646
```ruby
1647
# via a model
1648 1649
Article.any?
Article.many?
1650 1651

# via a named scope
1652 1653
Article.recent.any?
Article.recent.many?
1654 1655

# via a relation
1656 1657
Article.where(published: true).any?
Article.where(published: true).many?
1658 1659

# via an association
1660 1661
Article.first.categories.any?
Article.first.categories.many?
1662
```
1663

1664 1665
Calculations
------------
1666 1667 1668

This section uses count as an example method in this preamble, but the options described apply to all sub-sections.

P
Pratik Naik 已提交
1669
All calculation methods work directly on a model:
1670

1671
```ruby
P
Pratik Naik 已提交
1672 1673
Client.count
# SELECT count(*) AS count_all FROM clients
1674
```
1675

M
Matt Duncan 已提交
1676
Or on a relation:
1677

1678
```ruby
A
Agis Anastasopoulos 已提交
1679
Client.where(first_name: 'Ryan').count
P
Pratik Naik 已提交
1680
# SELECT count(*) AS count_all FROM clients WHERE (first_name = 'Ryan')
1681
```
1682

P
Pratik Naik 已提交
1683
You can also use various finder methods on a relation for performing complex calculations:
1684

1685
```ruby
1686
Client.includes("orders").where(first_name: 'Ryan', orders: { status: 'received' }).count
1687
```
1688 1689 1690

Which will execute:

1691
```sql
1692 1693 1694
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')
1695
```
1696

1697
### Count
1698

1699
If you want to see how many records are in your model's table you could call `Client.count` and that will return the number. If you want to be more specific and find all the clients with their age present in the database you can use `Client.count(:age)`.
1700

1701
For options, please see the parent section, [Calculations](#calculations).
1702

1703
### Average
1704

1705
If you want to see the average of a certain number in one of your tables you can call the `average` method on the class that relates to the table. This method call will look something like this:
1706

1707
```ruby
1708
Client.average("orders_count")
1709
```
1710 1711 1712

This will return a number (possibly a floating point number such as 3.14159265) representing the average value in the field.

1713
For options, please see the parent section, [Calculations](#calculations).
1714

1715
### Minimum
1716

1717
If you want to find the minimum value of a field in your table you can call the `minimum` method on the class that relates to the table. This method call will look something like this:
1718

1719
```ruby
1720
Client.minimum("age")
1721
```
1722

1723
For options, please see the parent section, [Calculations](#calculations).
1724

1725
### Maximum
1726

1727
If you want to find the maximum value of a field in your table you can call the `maximum` method on the class that relates to the table. This method call will look something like this:
1728

1729
```ruby
1730
Client.maximum("age")
1731
```
1732

1733
For options, please see the parent section, [Calculations](#calculations).
1734

1735
### Sum
1736

1737
If you want to find the sum of a field for all records in your table you can call the `sum` method on the class that relates to the table. This method call will look something like this:
1738

1739
```ruby
1740
Client.sum("orders_count")
1741
```
1742

1743
For options, please see the parent section, [Calculations](#calculations).
X
Xavier Noria 已提交
1744

1745 1746
Running EXPLAIN
---------------
X
Xavier Noria 已提交
1747 1748 1749

You can run EXPLAIN on the queries triggered by relations. For example,

1750
```ruby
1751
User.where(id: 1).joins(:articles).explain
1752
```
X
Xavier Noria 已提交
1753 1754 1755

may yield

1756
```
1757
EXPLAIN for: SELECT `users`.* FROM `users` INNER JOIN `articles` ON `articles`.`user_id` = `users`.`id` WHERE `users`.`id` = 1
1758 1759 1760 1761 1762 1763 1764 1765 1766 1767 1768 1769 1770
+----+-------------+----------+-------+---------------+
| 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 |
+---------+---------+-------+------+-------------+

X
Xavier Noria 已提交
1771
2 rows in set (0.00 sec)
1772
```
X
Xavier Noria 已提交
1773 1774 1775 1776

under MySQL.

Active Record performs a pretty printing that emulates the one of the database
V
Vijay Dev 已提交
1777
shells. So, the same query running with the PostgreSQL adapter would yield instead
X
Xavier Noria 已提交
1778

1779
```
1780
EXPLAIN for: SELECT "users".* FROM "users" INNER JOIN "articles" ON "articles"."user_id" = "users"."id" WHERE "users"."id" = 1
X
Xavier Noria 已提交
1781 1782 1783
                                  QUERY PLAN
------------------------------------------------------------------------------
 Nested Loop Left Join  (cost=0.00..37.24 rows=8 width=0)
1784
   Join Filter: (articles.user_id = users.id)
X
Xavier Noria 已提交
1785 1786
   ->  Index Scan using users_pkey on users  (cost=0.00..8.27 rows=1 width=4)
         Index Cond: (id = 1)
1787 1788
   ->  Seq Scan on articles  (cost=0.00..28.88 rows=8 width=4)
         Filter: (articles.user_id = 1)
X
Xavier Noria 已提交
1789
(6 rows)
1790
```
X
Xavier Noria 已提交
1791 1792

Eager loading may trigger more than one query under the hood, and some queries
1793
may need the results of previous ones. Because of that, `explain` actually
X
Xavier Noria 已提交
1794 1795
executes the query, and then asks for the query plans. For example,

1796
```ruby
1797
User.where(id: 1).includes(:articles).explain
1798
```
X
Xavier Noria 已提交
1799 1800 1801

yields

1802
```
1803
EXPLAIN for: SELECT `users`.* FROM `users`  WHERE `users`.`id` = 1
1804 1805 1806 1807 1808 1809 1810 1811 1812 1813 1814
+----+-------------+-------+-------+---------------+
| id | select_type | table | type  | possible_keys |
+----+-------------+-------+-------+---------------+
|  1 | SIMPLE      | users | const | PRIMARY       |
+----+-------------+-------+-------+---------------+
+---------+---------+-------+------+-------+
| key     | key_len | ref   | rows | Extra |
+---------+---------+-------+------+-------+
| PRIMARY | 4       | const |    1 |       |
+---------+---------+-------+------+-------+

X
Xavier Noria 已提交
1815
1 row in set (0.00 sec)
1816

1817
EXPLAIN for: SELECT `articles`.* FROM `articles`  WHERE `articles`.`user_id` IN (1)
1818 1819 1820 1821 1822 1823 1824 1825 1826 1827 1828 1829
+----+-------------+----------+------+---------------+
| id | select_type | table    | type | possible_keys |
+----+-------------+----------+------+---------------+
|  1 | SIMPLE      | articles | ALL  | NULL          |
+----+-------------+----------+------+---------------+
+------+---------+------+------+-------------+
| key  | key_len | ref  | rows | Extra       |
+------+---------+------+------+-------------+
| NULL | NULL    | NULL |    1 | Using where |
+------+---------+------+------+-------------+


X
Xavier Noria 已提交
1830
1 row in set (0.00 sec)
1831
```
X
Xavier Noria 已提交
1832 1833

under MySQL.
1834

1835
### Interpreting EXPLAIN
1836 1837 1838 1839

Interpretation of the output of EXPLAIN is beyond the scope of this guide. The
following pointers may be helpful:

1840
* SQLite3: [EXPLAIN QUERY PLAN](http://www.sqlite.org/eqp.html)
1841

1842
* MySQL: [EXPLAIN Output Format](http://dev.mysql.com/doc/refman/5.6/en/explain-output.html)
1843

1844
* PostgreSQL: [Using EXPLAIN](http://www.postgresql.org/docs/current/static/using-explain.html)