diff options
Diffstat (limited to 'guides/source/active_record_querying.md')
-rw-r--r-- | guides/source/active_record_querying.md | 221 |
1 files changed, 91 insertions, 130 deletions
diff --git a/guides/source/active_record_querying.md b/guides/source/active_record_querying.md index 486e7b80ff..e1a465c64f 100644 --- a/guides/source/active_record_querying.md +++ b/guides/source/active_record_querying.md @@ -93,9 +93,9 @@ The primary operation of `Model.find(options)` can be summarized as: 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 +109,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. +The `find` method will raise an `ActiveRecord::RecordNotFound` exception if no matching record is found. -#### `take` - -`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 +215,56 @@ 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. +The `last` method 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. 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 - -`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">] -``` - -The SQL equivalent of the above is: - -```sql -SELECT * FROM clients ORDER BY id ASC LIMIT 2 +Client.where(first_name: 'Lifo').take ``` -#### 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 +276,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 +292,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 ``` @@ -360,7 +316,7 @@ 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 ``` @@ -372,7 +328,7 @@ For example, to send newsletters only to users with the primary key starting fro ```ruby User.find_each(start: 2000, batch_size: 5000) do |user| - NewsLetter.weekly_deliver(user) + NewsMailer.weekly(user).deliver_now end ``` @@ -384,16 +340,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` and `:start` options as `find_each`. Conditions ---------- @@ -707,7 +661,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 +674,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 +713,6 @@ The `reorder` method overrides the default scope order. For example: ```ruby class Article < ActiveRecord::Base - .. - .. has_many :comments, -> { order('posted_at DESC') } end @@ -1487,6 +1439,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 +1453,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) |