diff options
Diffstat (limited to 'guides/source/active_record_querying.md')
| -rw-r--r-- | guides/source/active_record_querying.md | 311 |
1 files changed, 173 insertions, 138 deletions
diff --git a/guides/source/active_record_querying.md b/guides/source/active_record_querying.md index 486e7b80ff..ad5103da69 100644 --- a/guides/source/active_record_querying.md +++ b/guides/source/active_record_querying.md @@ -1,3 +1,5 @@ +**DO NOT READ THIS FILE ON GITHUB, GUIDES ARE PUBLISHED ON http://guides.rubyonrails.org.** + Active Record Query Interface ============================= @@ -9,6 +11,7 @@ After reading this guide, you will know: * 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. +* How to use method chaining to use multiple ActiveRecord methods together. * How to check for the existence of particular records. * How to perform various calculations on Active Record models. * How to run EXPLAIN on relations. @@ -87,15 +90,15 @@ The primary operation of `Model.find(options)` can be summarized as: * 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. -* Run `after_find` callbacks, if any. +* Run `after_find` and then `after_initialize` callbacks, if any. ### Retrieving a Single Object Active Record provides several different ways of retrieving a single object. -#### Using a Primary Key +#### `find` -Using `Model.find(primary_key)`, you can retrieve the object corresponding to the specified _primary key_ that matches any supplied options. For example: +Using the `find` method, you can retrieve the object corresponding to the specified _primary key_ that matches any supplied options. For example: ```ruby # Find the client with primary key (id) 10. @@ -109,119 +112,103 @@ The SQL equivalent of the above is: SELECT * FROM clients WHERE (clients.id = 10) LIMIT 1 ``` -`Model.find(primary_key)` will raise an `ActiveRecord::RecordNotFound` exception if no matching record is found. - -#### `take` +The `find` method will raise an `ActiveRecord::RecordNotFound` exception if no matching record is found. -`Model.take` retrieves a record without any implicit ordering. For example: +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 -client = Client.take -# => #<Client id: 1, first_name: "Lifo"> +# 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 LIMIT 1 +SELECT * FROM clients WHERE (clients.id IN (1,10)) ``` -`Model.take` returns `nil` if no record is found and no exception will be raised. - -TIP: The retrieved record may vary depending on the database engine. +WARNING: The `find` method will raise an `ActiveRecord::RecordNotFound` exception unless a matching record is found for **all** of the supplied primary keys. -#### `first` +#### `take` -`Model.first` finds the first record ordered by the primary key. For example: +The `take` method retrieves a record without any implicit ordering. For example: ```ruby -client = Client.first +client = Client.take # => #<Client id: 1, first_name: "Lifo"> ``` The SQL equivalent of the above is: ```sql -SELECT * FROM clients ORDER BY clients.id ASC LIMIT 1 +SELECT * FROM clients LIMIT 1 ``` -`Model.first` returns `nil` if no matching record is found and no exception will be raised. - -#### `last` +The `take` method returns `nil` if no record is found and no exception will be raised. -`Model.last` finds the last record ordered by the primary key. For example: +You can pass in a numerical argument to the `take` method to return up to that number of results. For example ```ruby -client = Client.last -# => #<Client id: 221, first_name: "Russel"> +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 ORDER BY clients.id DESC LIMIT 1 -``` - -`Model.last` returns `nil` if no matching record is found and no exception will be raised. - -#### `find_by` - -`Model.find_by` finds the first record matching some conditions. For example: - -```ruby -Client.find_by first_name: 'Lifo' -# => #<Client id: 1, first_name: "Lifo"> - -Client.find_by first_name: 'Jon' -# => nil +SELECT * FROM clients LIMIT 2 ``` -It is equivalent to writing: +The `take!` method behaves exactly like `take`, except that it will raise `ActiveRecord::RecordNotFound` if no matching record is found. -```ruby -Client.where(first_name: 'Lifo').take -``` +TIP: The retrieved record may vary depending on the database engine. -#### `take!` +#### `first` -`Model.take!` retrieves a record without any implicit ordering. For example: +The `first` method finds the first record ordered by the primary key. For example: ```ruby -client = Client.take! +client = Client.first # => #<Client id: 1, first_name: "Lifo"> ``` The SQL equivalent of the above is: ```sql -SELECT * FROM clients LIMIT 1 +SELECT * FROM clients ORDER BY clients.id ASC LIMIT 1 ``` -`Model.take!` raises `ActiveRecord::RecordNotFound` if no matching record is found. - -#### `first!` +The `first` method returns `nil` if no matching record is found and no exception will be raised. -`Model.first!` finds the first record ordered by the primary key. For example: +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! -# => #<Client id: 1, first_name: "Lifo"> +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 1 +SELECT * FROM clients ORDER BY clients.id ASC LIMIT 3 ``` -`Model.first!` raises `ActiveRecord::RecordNotFound` if no matching record is found. +The `first!` method behaves exactly like `first`, except that it will raise `ActiveRecord::RecordNotFound` if no matching record is found. -#### `last!` +#### `last` -`Model.last!` finds the last record ordered by the primary key. For example: +The `last` method finds the last record ordered by the primary key. For example: ```ruby -client = Client.last! +client = Client.last # => #<Client id: 221, first_name: "Russel"> ``` @@ -231,92 +218,62 @@ The SQL equivalent of the above is: SELECT * FROM clients ORDER BY clients.id DESC LIMIT 1 ``` -`Model.last!` raises `ActiveRecord::RecordNotFound` if no matching record is found. - -#### `find_by!` - -`Model.find_by!` finds the first record matching some conditions. It raises `ActiveRecord::RecordNotFound` if no matching record is found. For example: - -```ruby -Client.find_by! first_name: 'Lifo' -# => #<Client id: 1, first_name: "Lifo"> - -Client.find_by! first_name: 'Jon' -# => ActiveRecord::RecordNotFound -``` - -It is equivalent to writing: - -```ruby -Client.where(first_name: 'Lifo').take! -``` - -### Retrieving Multiple Objects - -#### Using Multiple Primary Keys +The `last` method returns `nil` if no matching record is found and no exception will be raised. -`Model.find(array_of_primary_key)` accepts an array of _primary keys_, returning an array containing all of the matching records for the supplied _primary keys_. For example: +You can pass in a numerical argument to the `last` method to return up to that number of results. 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">] +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 WHERE (clients.id IN (1,10)) +SELECT * FROM clients ORDER BY clients.id DESC LIMIT 3 ``` -WARNING: `Model.find(array_of_primary_key)` will raise an `ActiveRecord::RecordNotFound` exception unless a matching record is found for **all** of the supplied primary keys. +The `last!` method behaves exactly like `last`, except that it will raise `ActiveRecord::RecordNotFound` if no matching record is found. -#### take +#### `find_by` -`Model.take(limit)` retrieves the first number of records specified by `limit` without any explicit ordering: +The `find_by` method finds the first record matching some conditions. For example: ```ruby -Client.take(2) -# => [#<Client id: 1, first_name: "Lifo">, - #<Client id: 2, first_name: "Raf">] -``` - -The SQL equivalent of the above is: +Client.find_by first_name: 'Lifo' +# => #<Client id: 1, first_name: "Lifo"> -```sql -SELECT * FROM clients LIMIT 2 +Client.find_by first_name: 'Jon' +# => nil ``` -#### first - -`Model.first(limit)` finds the first number of records specified by `limit` ordered by primary key: +It is equivalent to writing: ```ruby -Client.first(2) -# => [#<Client id: 1, first_name: "Lifo">, - #<Client id: 2, first_name: "Raf">] +Client.where(first_name: 'Lifo').take ``` The SQL equivalent of the above is: ```sql -SELECT * FROM clients ORDER BY id ASC LIMIT 2 +SELECT * FROM clients WHERE (clients.first_name = 'Lifo') LIMIT 1 ``` -#### last - -`Model.last(limit)` finds the number of records specified by `limit` ordered by primary key in descending order: +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.last(2) -# => [#<Client id: 10, first_name: "Ryan">, - #<Client id: 9, first_name: "John">] +Client.find_by! first_name: 'does not exist' +# => ActiveRecord::RecordNotFound ``` -The SQL equivalent of the above is: +This is equivalent to writing: -```sql -SELECT * FROM clients ORDER BY id DESC LIMIT 2 +```ruby +Client.where(first_name: 'does not exist').take! ``` ### Retrieving Multiple Objects in Batches @@ -328,7 +285,7 @@ This may appear straightforward: ```ruby # This is very inefficient when the users table has thousands of rows. User.all.each do |user| - NewsLetter.weekly_deliver(user) + NewsMailer.weekly(user).deliver_now end ``` @@ -344,7 +301,15 @@ The `find_each` method retrieves a batch of records and then yields _each_ recor ```ruby User.find_each do |user| - NewsLetter.weekly_deliver(user) + NewsMailer.weekly(user).deliver_now +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| + NewsMailer.weekly(user).deliver_now end ``` @@ -352,7 +317,7 @@ end 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`. -Two additional options, `:batch_size` and `:start`, are available as well. +Two additional options, `:batch_size` and `:begin_at`, are available as well. **`:batch_size`** @@ -360,23 +325,36 @@ The `:batch_size` option allows you to specify the number of records to be retri ```ruby User.find_each(batch_size: 5000) do |user| - NewsLetter.weekly_deliver(user) + NewsMailer.weekly(user).deliver_now end ``` -**`:start`** +**`:begin_at`** -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. +By default, records are fetched in ascending order of the primary key, which must be an integer. The `:begin_at` 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. For example, to send newsletters only to users with the primary key starting from 2000, and to retrieve them in batches of 5000: ```ruby -User.find_each(start: 2000, batch_size: 5000) do |user| - NewsLetter.weekly_deliver(user) +User.find_each(begin_at: 2000, batch_size: 5000) do |user| + NewsMailer.weekly(user).deliver_now end ``` -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. +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 `:begin_at` option on each worker. + +**`:end_at`** + +Similar to the `:begin_at` option, `:end_at` allows you to configure the last ID of the sequence whenever the highest ID is not the one you need. +This would be useful, for example, if you wanted to run a batch process, using a subset of records based on `:begin_at` and `:end_at` + +For example, to send newsletters only to users with the primary key starting from 2000 upto 10000 and to retrieve them in batches of 1000: + +```ruby +User.find_each(begin_at: 2000, end_at: 10000, batch_size: 5000) do |user| + NewsMailer.weekly(user).deliver_now +end +``` #### `find_in_batches` @@ -384,16 +362,14 @@ The `find_in_batches` method is similar to `find_each`, since both retrieve batc ```ruby # Give add_invoices an array of 1000 invoices at a time -Invoice.find_in_batches(include: :invoice_lines) do |invoices| +Invoice.find_in_batches do |invoices| export.add_invoices(invoices) end ``` -NOTE: The `:include` option allows you to name associations that should be loaded alongside with the models. - ##### Options for `find_in_batches` -The `find_in_batches` method accepts the same `:batch_size` and `:start` options as `find_each`, as well as most of the options allowed by the regular `find` method, except for `:order` and `:limit`, which are reserved for internal use by `find_in_batches`. +The `find_in_batches` method accepts the same `:batch_size`, `:begin_at` and `:end_at` options as `find_each`. Conditions ---------- @@ -707,7 +683,7 @@ Overriding Conditions You can specify certain conditions to be removed using the `unscope` method. For example: ```ruby -Article.where('id > 10').limit(20).order('id asc').except(:order) +Article.where('id > 10').limit(20).order('id asc').unscope(:order) ``` The SQL that would be executed: @@ -720,7 +696,7 @@ SELECT * FROM articles WHERE id > 10 ORDER BY id asc LIMIT 20 ``` -You can additionally unscope specific where clauses. For example: +You can also unscope specific `where` clauses. For example: ```ruby Article.where(id: 10, trashed: false).unscope(where: :id) @@ -759,8 +735,6 @@ The `reorder` method overrides the default scope order. For example: ```ruby class Article < ActiveRecord::Base - .. - .. has_many :comments, -> { order('posted_at DESC') } end @@ -1173,7 +1147,7 @@ This would generate a query which contains a `LEFT OUTER JOIN` whereas the If there was no `where` condition, this would generate the normal set of two queries. NOTE: Using `where` like this will only work when you pass it a Hash. For -SQL-fragments you need use `references` to force joined tables: +SQL-fragments you need to use `references` to force joined tables: ```ruby Article.includes(:comments).where("comments.visible = true").references(:comments) @@ -1314,7 +1288,7 @@ User.active.where(state: 'finished') # SELECT "users".* FROM "users" WHERE "users"."state" = 'active' AND "users"."state" = 'finished' ``` -If we do want the `last where clause` to win then `Relation#merge` can +If we do want the last `where` clause to win then `Relation#merge` can be used. ```ruby @@ -1375,14 +1349,66 @@ You can specify an exclamation point (`!`) on the end of the dynamic finders to 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)`. +Understanding The Method Chaining +--------------------------------- + +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. + +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. + +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. + +### Retrieving filtered data from multiple tables + +```ruby +Person + .select('people.id, people.name, comments.text') + .joins(:comments) + .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' +``` + +### 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 +``` + +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 +``` + +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. + Find or Build a New Object -------------------------- -NOTE: Some dynamic finders have been deprecated in Rails 4.0 and will be -removed in Rails 4.1. The best practice is to use Active Record scopes -instead. You can find the deprecation gem at -https://github.com/rails/activerecord-deprecated_finders - 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. ### `find_or_create_by` @@ -1487,6 +1513,11 @@ If you'd like to use your own SQL to find records in a table you can use `find_b Client.find_by_sql("SELECT * FROM clients INNER JOIN orders ON clients.id = orders.client_id ORDER BY clients.created_at desc") +# => [ + #<Client id: 1, first_name: "Lucas" >, + #<Client id: 2, first_name: "Jan" >, + # ... +] ``` `find_by_sql` provides you with a simple way of making custom calls to the database and retrieving instantiated objects. @@ -1496,12 +1527,16 @@ Client.find_by_sql("SELECT * FROM clients `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. ```ruby -Client.connection.select_all("SELECT * FROM clients WHERE id = '1'") +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"} +] ``` ### `pluck` -`pluck` can be used to query a 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. +`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. ```ruby Client.where(active: true).pluck(:id) |