diff options
Diffstat (limited to 'guides/source/active_record_querying.md')
| -rw-r--r-- | guides/source/active_record_querying.md | 94 |
1 files changed, 55 insertions, 39 deletions
diff --git a/guides/source/active_record_querying.md b/guides/source/active_record_querying.md index 31220f9be2..91cc175095 100644 --- a/guides/source/active_record_querying.md +++ b/guides/source/active_record_querying.md @@ -1,4 +1,4 @@ -**DO NOT READ THIS FILE ON GITHUB, GUIDES ARE PUBLISHED ON http://guides.rubyonrails.org.** +**DO NOT READ THIS FILE ON GITHUB, GUIDES ARE PUBLISHED ON https://guides.rubyonrails.org.** Active Record Query Interface ============================= @@ -118,7 +118,7 @@ You can also use this method to query for multiple objects. Call the `find` meth ```ruby # Find the clients with primary keys 1 and 10. -client = Client.find([1, 10]) # Or even Client.find(1, 10) +clients = Client.find([1, 10]) # Or even Client.find(1, 10) # => [#<Client id: 1, first_name: "Lifo">, #<Client id: 10, first_name: "Ryan">] ``` @@ -150,7 +150,7 @@ The `take` method returns `nil` if no record is found and no exception will be r 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) +clients = Client.take(2) # => [ # #<Client id: 1, first_name: "Lifo">, # #<Client id: 220, first_name: "Sara"> @@ -189,7 +189,7 @@ If your [default scope](active_record_querying.html#applying-a-default-scope) co 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) +clients = Client.first(3) # => [ # #<Client id: 1, first_name: "Lifo">, # #<Client id: 2, first_name: "Fifo">, @@ -240,7 +240,7 @@ If your [default scope](active_record_querying.html#applying-a-default-scope) co 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) +clients = Client.last(3) # => [ # #<Client id: 219, first_name: "James">, # #<Client id: 220, first_name: "Sara">, @@ -368,7 +368,7 @@ end **`:start`** -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. 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. For example, to send newsletters only to users with the primary key starting from 2000: @@ -414,7 +414,7 @@ end `find_in_batches` works on model classes, as seen above, and also on relations: ```ruby -Invoice.pending.find_in_batches do |invoice| +Invoice.pending.find_in_batches do |invoices| pending_invoices_export.add_invoices(invoices) end ``` @@ -486,7 +486,7 @@ This makes for clearer readability if you have a large number of variable condit 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 qualified and the values of how you want to qualify them: -NOTE: Only equality, range and subset checking are possible with Hash conditions. +NOTE: Only equality, range, and subset checking are possible with Hash conditions. #### Equality Conditions @@ -513,8 +513,6 @@ Article.where(author: author) Author.joins(:articles).where(articles: { author: author }) ``` -NOTE: The values cannot be symbols. For example, you cannot do `Client.where(status: :active)`. - #### Range Conditions ```ruby @@ -557,6 +555,19 @@ In other words, this query can be generated by calling `where` with no argument, SELECT * FROM clients WHERE (clients.locked != 1) ``` +### OR Conditions + +`OR` conditions between two relations can be built by calling `or` on the first +relation, and passing the second one as an argument. + +```ruby +Client.where(locked: true).or(Client.where(orders_count: [1,3,5])) +``` + +```sql +SELECT * FROM clients WHERE (clients.locked = 1 OR clients.orders_count IN (1,3,5)) +``` + Ordering -------- @@ -790,7 +801,7 @@ The SQL that would be executed: SELECT * FROM articles WHERE id > 10 ORDER BY id DESC # Original query without `only` -SELECT "articles".* FROM "articles" WHERE (id > 10) ORDER BY id desc LIMIT 20 +SELECT * FROM articles WHERE id > 10 ORDER BY id DESC LIMIT 20 ``` @@ -809,14 +820,14 @@ Article.find(10).comments.reorder('name') The SQL that would be executed: ```sql -SELECT * FROM articles WHERE id = 10 +SELECT * FROM articles WHERE id = 10 LIMIT 1 SELECT * FROM comments WHERE article_id = 10 ORDER BY name ``` In the case where the `reorder` clause is not used, the SQL executed would be: ```sql -SELECT * FROM articles WHERE id = 10 +SELECT * FROM articles WHERE id = 10 LIMIT 1 SELECT * FROM comments WHERE article_id = 10 ORDER BY posted_at DESC ``` @@ -953,9 +964,6 @@ class Client < ApplicationRecord end ``` -NOTE: Please note that the optimistic locking will be ignored if you update the -locking column's value. - ### Pessimistic Locking 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. @@ -1083,7 +1091,7 @@ This produces: ```sql SELECT articles.* FROM articles - INNER JOIN categories ON articles.category_id = categories.id + INNER JOIN categories ON categories.id = articles.category_id INNER JOIN comments ON comments.article_id = articles.id ``` @@ -1269,16 +1277,6 @@ class Article < ApplicationRecord end ``` -This is exactly the same as defining a class method, and which you use is a matter of personal preference: - -```ruby -class Article < ApplicationRecord - def self.published - where(published: true) - end -end -``` - Scopes are also chainable within scopes: ```ruby @@ -1384,8 +1382,9 @@ class Client < ApplicationRecord end ``` -NOTE: The `default_scope` is also applied while creating/building a record. -It is not applied while updating a record. E.g.: +NOTE: The `default_scope` is also applied while creating/building a record +when the scope arguments are given as a `Hash`. It is not applied while +updating a record. E.g.: ```ruby class Client < ApplicationRecord @@ -1396,6 +1395,17 @@ Client.new # => #<Client id: nil, active: true> Client.unscoped.new # => #<Client id: nil, active: nil> ``` +Be aware that, when given in the `Array` format, `default_scope` query arguments +cannot be converted to a `Hash` for default attribute assignment. E.g.: + +```ruby +class Client < ApplicationRecord + default_scope { where("active = ?", true) } +end + +Client.new # => #<Client id: nil, active: nil> +``` + ### Merging of scopes Just like `where` clauses scopes are merged using `AND` conditions. @@ -1519,7 +1529,7 @@ Read the full documentation about enums Understanding The Method Chaining --------------------------------- -The Active Record pattern implements [Method Chaining](http://en.wikipedia.org/wiki/Method_chaining), +The Active Record pattern implements [Method Chaining](https://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 @@ -1547,7 +1557,7 @@ 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' +WHERE comments.created_at > '2015-01-01' ``` ### Retrieving specific data from multiple tables @@ -1692,10 +1702,10 @@ Client.find_by_sql("SELECT * FROM clients ### `select_all` -`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. +`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. This method will return an instance of `ActiveRecord::Result` class and calling `to_hash` on this object would return you an array of hashes where each hash indicates a record. ```ruby -Client.connection.select_all("SELECT first_name, created_at FROM clients WHERE id = '1'") +Client.connection.select_all("SELECT first_name, created_at FROM clients WHERE id = '1'").to_hash # => [ # {"first_name"=>"Rafael", "created_at"=>"2012-11-10 23:23:45.281189"}, # {"first_name"=>"Eileen", "created_at"=>"2013-12-09 11:22:35.221282"} @@ -1757,6 +1767,12 @@ Client.pluck(:name) # => ["David", "Jeremy", "Jose"] ``` +You are not limited to querying fields from a single table, you can query multiple tables as well. + +``` +Client.joins(:comments, :categories).pluck("clients.email, comments.title, categories.name") +``` + 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: @@ -1851,14 +1867,14 @@ All calculation methods work directly on a model: ```ruby Client.count -# SELECT count(*) AS count_all FROM clients +# SELECT COUNT(*) FROM clients ``` Or on a relation: ```ruby Client.where(first_name: 'Ryan').count -# SELECT count(*) AS count_all FROM clients WHERE (first_name = 'Ryan') +# SELECT COUNT(*) FROM clients WHERE (first_name = 'Ryan') ``` You can also use various finder methods on a relation for performing complex calculations: @@ -1870,9 +1886,9 @@ Client.includes("orders").where(first_name: 'Ryan', orders: { status: 'received' Which will execute: ```sql -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') +SELECT COUNT(DISTINCT clients.id) FROM clients + LEFT OUTER JOIN orders ON orders.client_id = clients.id + WHERE (clients.first_name = 'Ryan' AND orders.status = 'received') ``` ### Count @@ -2025,4 +2041,4 @@ following pointers may be helpful: * MariaDB: [EXPLAIN](https://mariadb.com/kb/en/mariadb/explain/) -* PostgreSQL: [Using EXPLAIN](http://www.postgresql.org/docs/current/static/using-explain.html) +* PostgreSQL: [Using EXPLAIN](https://www.postgresql.org/docs/current/static/using-explain.html) |