diff options
Diffstat (limited to 'guides/source/active_record_querying.md')
| -rw-r--r-- | guides/source/active_record_querying.md | 65 |
1 files changed, 58 insertions, 7 deletions
diff --git a/guides/source/active_record_querying.md b/guides/source/active_record_querying.md index 02055e59f0..5fb030fad4 100644 --- a/guides/source/active_record_querying.md +++ b/guides/source/active_record_querying.md @@ -59,11 +59,13 @@ To retrieve objects from the database, Active Record provides several finder met The methods are: +* `annotate` * `find` * `create_with` * `distinct` * `eager_load` * `extending` +* `extract_associated` * `from` * `group` * `having` @@ -74,11 +76,13 @@ The methods are: * `lock` * `none` * `offset` +* `optimizer_hints` * `order` * `preload` * `readonly` * `references` * `reorder` +* `reselect` * `reverse_order` * `select` * `where` @@ -611,7 +615,8 @@ If you want to call `order` multiple times, subsequent orders will be appended t Client.order("orders_count ASC").order("created_at DESC") # SELECT * FROM clients ORDER BY orders_count ASC, created_at DESC ``` -WARNING: If you are using **MySQL 5.7.5** and above, then on selecting fields from a result set using methods like `select`, `pluck` and `ids`; the `order` method will raise an `ActiveRecord::StatementInvalid` exception unless the field(s) used in `order` clause are included in the select list. See the next section for selecting fields from the result set. + +WARNING: In most database systems, on selecting fields with `distinct` from a result set using methods like `select`, `pluck` and `ids`; the `order` method will raise an `ActiveRecord::StatementInvalid` exception unless the field(s) used in `order` clause are included in the select list. See the next section for selecting fields from the result set. Selecting Specific Fields ------------------------- @@ -623,6 +628,8 @@ To select only a subset of fields from the result set, you can specify the subse For example, to select only `viewable_by` and `locked` columns: ```ruby +Client.select(:viewable_by, :locked) +# OR Client.select("viewable_by, locked") ``` @@ -805,6 +812,32 @@ SELECT * FROM articles WHERE id > 10 ORDER BY id DESC LIMIT 20 ``` +### `reselect` + +The `reselect` method overrides an existing select statement. For example: + +```ruby +Post.select(:title, :body).reselect(:created_at) +``` + +The SQL that would be executed: + +```sql +SELECT `posts`.`created_at` FROM `posts` +``` + +In case the `reselect` clause is not used, + +```ruby +Post.select(:title, :body).select(:created_at) +``` + +the SQL executed would be: + +```sql +SELECT `posts`.`title`, `posts`.`body`, `posts`.`created_at` FROM `posts` +``` + ### `reorder` The `reorder` method overrides the default scope order. For example: @@ -1267,7 +1300,7 @@ This is because it is ambiguous whether they should appear on the parent record, Scopes ------ -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. +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 bodies should return an `ActiveRecord::Relation` or `nil` to allow for further methods (such as other scopes) to be called on it. 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: @@ -1524,7 +1557,7 @@ book.available? # => false ``` Read the full documentation about enums -[in the Rails API docs](http://api.rubyonrails.org/classes/ActiveRecord/Enum.html). +[in the Rails API docs](https://api.rubyonrails.org/classes/ActiveRecord/Enum.html). Understanding The Method Chaining --------------------------------- @@ -1702,10 +1735,13 @@ 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. 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. +`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_a` 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'").to_hash +Client.connection.select_all("SELECT first_name, created_at FROM clients WHERE id = '1'").to_a # => [ # {"first_name"=>"Rafael", "created_at"=>"2012-11-10 23:23:45.281189"}, # {"first_name"=>"Eileen", "created_at"=>"2013-12-09 11:22:35.221282"} @@ -1785,6 +1821,21 @@ Client.limit(1).pluck(:name) # => ["David"] ``` +NOTE: You should also know that using `pluck` will trigger eager loading if the relation object contains include values, even if the eager loading is not necessary for the query. For example: + +```ruby +# store association for reusing it +assoc = Company.includes(:account) +assoc.pluck(:id) +# SELECT "companies"."id" FROM "companies" LEFT OUTER JOIN "accounts" ON "accounts"."id" = "companies"."account_id" +``` + +One way to avoid this is to `unscope` the includes: + +```ruby +assoc.unscope(:includes).pluck(:id) +``` + ### `ids` `ids` can be used to pluck all the IDs for the relation using the table's primary key. @@ -2035,9 +2086,9 @@ under MySQL and MariaDB. Interpretation of the output of EXPLAIN is beyond the scope of this guide. The following pointers may be helpful: -* SQLite3: [EXPLAIN QUERY PLAN](http://www.sqlite.org/eqp.html) +* SQLite3: [EXPLAIN QUERY PLAN](https://www.sqlite.org/eqp.html) -* MySQL: [EXPLAIN Output Format](http://dev.mysql.com/doc/refman/5.7/en/explain-output.html) +* MySQL: [EXPLAIN Output Format](https://dev.mysql.com/doc/refman/5.7/en/explain-output.html) * MariaDB: [EXPLAIN](https://mariadb.com/kb/en/mariadb/explain/) |