diff options
Diffstat (limited to 'guides/source/active_record_querying.md')
| -rw-r--r-- | guides/source/active_record_querying.md | 95 |
1 files changed, 63 insertions, 32 deletions
diff --git a/guides/source/active_record_querying.md b/guides/source/active_record_querying.md index 4b4d70d3ce..ec31fa9d67 100644 --- a/guides/source/active_record_querying.md +++ b/guides/source/active_record_querying.md @@ -10,7 +10,7 @@ After reading this guide, you will know: * 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. +* How to use dynamic finder 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. @@ -69,6 +69,7 @@ The methods are: * `having` * `includes` * `joins` +* `left_outer_joins` * `limit` * `lock` * `none` @@ -80,7 +81,7 @@ The methods are: * `reorder` * `reverse_order` * `select` -* `uniq` +* `distinct` * `where` All of the above methods return an instance of `ActiveRecord::Relation`. @@ -184,6 +185,8 @@ SELECT * FROM clients ORDER BY clients.id ASC LIMIT 1 The `first` method returns `nil` if no matching record is found and no exception will be raised. +If your [default scope](active_record_querying.html#applying-a-default-scope) contains an order method, `first` will return the first record according to this ordering. + You can pass in a numerical argument to the `first` method to return up to that number of results. For example ```ruby @@ -220,6 +223,8 @@ SELECT * FROM clients ORDER BY clients.id DESC LIMIT 1 The `last` method returns `nil` if no matching record is found and no exception will be raised. +If your [default scope](active_record_querying.html#applying-a-default-scope) contains an order method, `last` will return the last record according to this ordering. + You can pass in a numerical argument to the `last` method to return up to that number of results. For example ```ruby @@ -341,8 +346,6 @@ User.find_each(begin_at: 2000, batch_size: 5000) do |user| 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 `: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. @@ -356,6 +359,10 @@ User.find_each(begin_at: 2000, end_at: 10000, batch_size: 5000) do |user| 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 `:begin_at` and `:end_at` options on each worker. + #### `find_in_batches` 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: @@ -390,7 +397,7 @@ Now what if that number could vary, say as an argument from somewhere? The find Client.where("orders_count = ?", params[:orders]) ``` -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. +Active Record will take the first argument as the conditions string and any additional arguments will replace the question marks `(?)` in it. If you want to specify multiple conditions: @@ -418,7 +425,7 @@ TIP: For more information on the dangers of SQL injection, see the [Ruby on Rail #### Placeholder Conditions -Similar to the `(?)` replacement style of params, you can also specify keys/values hash in your array conditions: +Similar to the `(?)` replacement style of params, you can also specify keys in your conditions string along with a corresponding keys/values hash: ```ruby Client.where("created_at >= :start_date AND created_at <= :end_date", @@ -429,7 +436,7 @@ This makes for clearer readability if you have a large number of variable condit ### Hash Conditions -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: +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. @@ -529,7 +536,7 @@ Client.order("orders_count ASC, created_at DESC") Client.order("orders_count ASC", "created_at DESC") ``` -If you want to call `order` multiple times e.g. in different context, new order will append previous one: +If you want to call `order` multiple times, subsequent orders will be appended to the first: ```ruby Client.order("orders_count ASC").order("created_at DESC") @@ -617,9 +624,9 @@ SELECT * FROM clients LIMIT 5 OFFSET 30 Group ----- -To apply a `GROUP BY` clause to the SQL fired by the finder, you can specify the `group` method on the find. +To apply a `GROUP BY` clause to the SQL fired by the finder, you can use the `group` method. -For example, if you want to find a collection of the dates orders were created on: +For example, if you want to find a collection of the dates on which orders were created: ```ruby Order.select("date(created_at) as ordered_date, sum(price) as total_price").group("date(created_at)") @@ -637,7 +644,7 @@ GROUP BY date(created_at) ### Total of grouped items -To get the total of grouped items on a single query call `count` after the `group`. +To get the total of grouped items on a single query, call `count` after the `group`. ```ruby Order.group(:status).count @@ -673,7 +680,7 @@ GROUP BY date(created_at) HAVING sum(price) > 100 ``` -This will return single order objects for each day, but only those that are ordered more than $100 in a day. +This returns the date and total price for each order object, grouped by the day they were ordered and where the price is more than $100. Overriding Conditions --------------------- @@ -703,8 +710,7 @@ Article.where(id: 10, trashed: false).unscope(where: :id) # SELECT "articles".* FROM "articles" WHERE trashed = 0 ``` -A relation which has used `unscope` will affect any relation it is -merged in to: +A relation which has used `unscope` will affect any relation into which it is merged: ```ruby Article.order('id asc').merge(Article.unscope(:order)) @@ -748,7 +754,7 @@ SELECT * FROM articles WHERE id = 10 SELECT * FROM comments WHERE article_id = 10 ORDER BY name ``` -In case the `reorder` clause is not used, the SQL executed would be: +In the case where the `reorder` clause is not used, the SQL executed would be: ```sql SELECT * FROM articles WHERE id = 10 @@ -837,7 +843,7 @@ end Readonly Objects ---------------- -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. +Active Record provides the `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. ```ruby client = Client.readonly.first @@ -934,25 +940,30 @@ end Joining Tables -------------- -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. +Active Record provides two finder methods for specifying `JOIN` clauses on the +resulting SQL: `joins` and `left_outer_joins`. +While `joins` should be used for `INNER JOIN` or custom queries, +`left_outer_joins` is used for queries using `LEFT OUTER JOIN`. -### Using a String SQL Fragment +### `joins` + +There are multiple ways to use the `joins` method. + +#### Using a String SQL Fragment You can just supply the raw SQL specifying the `JOIN` clause to `joins`: ```ruby -Client.joins('LEFT OUTER JOIN addresses ON addresses.client_id = clients.id') +Author.joins("INNER JOIN posts ON posts.author_id = author.id AND posts.published = 't'") ``` This will result in the following SQL: ```sql -SELECT clients.* FROM clients LEFT OUTER JOIN addresses ON addresses.client_id = clients.id +SELECT clients.* FROM clients INNER JOIN posts ON posts.author_id = author.id AND posts.published = 't' ``` -### Using Array/Hash of Named Associations - -WARNING: This method only works with `INNER JOIN`. +#### Using Array/Hash of Named Associations 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. @@ -985,7 +996,7 @@ end Now all of the following will produce the expected join queries using `INNER JOIN`: -#### Joining a Single Association +##### Joining a Single Association ```ruby Category.joins(:articles) @@ -998,7 +1009,7 @@ SELECT categories.* FROM categories INNER JOIN articles ON articles.category_id = categories.id ``` -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`. +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).distinct`. #### Joining Multiple Associations @@ -1016,7 +1027,7 @@ SELECT articles.* FROM articles 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. -#### Joining Nested Associations (Single Level) +##### Joining Nested Associations (Single Level) ```ruby Article.joins(comments: :guest) @@ -1032,7 +1043,7 @@ SELECT articles.* FROM articles Or, in English: "return all articles that have a comment made by a guest." -#### Joining Nested Associations (Multiple Level) +##### Joining Nested Associations (Multiple Level) ```ruby Category.joins(articles: [{ comments: :guest }, :tags]) @@ -1048,9 +1059,9 @@ SELECT categories.* FROM categories INNER JOIN tags ON tags.article_id = articles.id ``` -### Specifying Conditions on the Joined Tables +#### Specifying Conditions on the Joined Tables -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: +You can specify conditions on the joined tables using the regular [Array](#array-conditions) and [String](#pure-string-conditions) conditions. [Hash conditions](#hash-conditions) provide a special syntax for specifying conditions for the joined tables: ```ruby time_range = (Time.now.midnight - 1.day)..Time.now.midnight @@ -1066,6 +1077,26 @@ Client.joins(:orders).where(orders: { created_at: time_range }) This will find all clients who have orders that were created yesterday, again using a `BETWEEN` SQL expression. +### `left_outer_joins` + +If you want to select a set of records whether or not they have associated +records you can use the `left_outer_joins` method. + +```ruby +Author.left_outer_joins(:posts).uniq.select('authors.*, COUNT(posts.*) AS posts_count').group('authors.id') +``` + +Which produces: + +```sql +SELECT DISTINCT authors.*, COUNT(posts.*) AS posts_count FROM "authors" +LEFT OUTER JOIN posts ON posts.author_id = authors.id GROUP BY authors.id +``` + +Which means: "return all authors with their count of posts, whether or not they +have any posts at all" + + Eager Loading Associations -------------------------- @@ -1089,7 +1120,7 @@ This code looks fine at the first sight. But the problem lies within the total n 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. -Revisiting the above case, we could rewrite `Client.limit(10)` to use eager load addresses: +Revisiting the above case, we could rewrite `Client.limit(10)` to eager load addresses: ```ruby clients = Client.includes(:address).limit(10) @@ -1426,7 +1457,7 @@ It's common that you need to find a record or create it if it doesn't exist. You ### `find_or_create_by` -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. +The `find_or_create_by` method checks whether a record with the specified attributes exists. If it doesn't, then `create` is called. Let's see an example. Suppose you want to find a client named 'Andy', and if there's none, create one. You can do so by running: @@ -1866,6 +1897,6 @@ following pointers may be helpful: * SQLite3: [EXPLAIN QUERY PLAN](http://www.sqlite.org/eqp.html) -* MySQL: [EXPLAIN Output Format](http://dev.mysql.com/doc/refman/5.6/en/explain-output.html) +* MySQL: [EXPLAIN Output Format](http://dev.mysql.com/doc/refman/5.7/en/explain-output.html) * PostgreSQL: [Using EXPLAIN](http://www.postgresql.org/docs/current/static/using-explain.html) |