diff options
Diffstat (limited to 'guides/source/active_record_querying.md')
| -rw-r--r-- | guides/source/active_record_querying.md | 228 |
1 files changed, 146 insertions, 82 deletions
diff --git a/guides/source/active_record_querying.md b/guides/source/active_record_querying.md index 031a203f08..2a76df156c 100644 --- a/guides/source/active_record_querying.md +++ b/guides/source/active_record_querying.md @@ -436,7 +436,7 @@ to this code: Client.where("orders_count = #{params[:orders]}") ``` -because of argument safety. Putting the variable directly into the conditions string will pass the variable to the database **as-is**. This means that it will be an unescaped variable directly from a user who may have malicious intent. If you do this, you put your entire database at risk because once a user finds out he or she can exploit your database they can do just about anything to it. Never ever put your arguments directly inside the conditions string. +because of argument safety. Putting the variable directly into the conditions string will pass the variable to the database **as-is**. This means that it will be an unescaped variable directly from a user who may have malicious intent. If you do this, you put your entire database at risk because once a user finds out they can exploit your database they can do just about anything to it. Never ever put your arguments directly inside the conditions string. TIP: For more information on the dangers of SQL injection, see the [Ruby on Rails Security Guide](security.html#sql-injection). @@ -473,7 +473,7 @@ In the case of a belongs_to relationship, an association key can be used to spec ```ruby Post.where(author: author) -Author.joins(:posts).where(posts: {author: author}) +Author.joins(:posts).where(posts: { author: author }) ``` NOTE: The values cannot be symbols. For example, you cannot do `Client.where(status: :active)`. @@ -524,12 +524,18 @@ To retrieve records from the database in a specific order, you can use the `orde For example, if you're getting a set of records and want to order them in ascending order by the `created_at` field in your table: ```ruby +Client.order(:created_at) +# OR Client.order("created_at") ``` You could specify `ASC` or `DESC` as well: ```ruby +Client.order(created_at: :desc) +# OR +Client.order(created_at: :asc) +# OR Client.order("created_at DESC") # OR Client.order("created_at ASC") @@ -538,16 +544,20 @@ Client.order("created_at ASC") Or ordering by multiple fields: ```ruby +Client.order(orders_count: :asc, created_at: :desc) +# OR +Client.order(:orders_count, created_at: :desc) +# OR Client.order("orders_count ASC, created_at DESC") # OR Client.order("orders_count ASC", "created_at DESC") ``` -If you want to call `order` multiple times e.g. in different context, new order will prepend previous one +If you want to call `order` multiple times e.g. in different context, new order will append previous one ```ruby Client.order("orders_count ASC").order("created_at DESC") -# SELECT * FROM clients ORDER BY created_at DESC, orders_count ASC +# SELECT * FROM clients ORDER BY orders_count ASC, created_at DESC ``` Selecting Specific Fields @@ -675,9 +685,9 @@ This will return single order objects for each day, but only those that are orde Overriding Conditions --------------------- -### `except` +### `unscope` -You can specify certain conditions to be excepted by using the `except` method. For example: +You can specify certain conditions to be removed using the `unscope` method. For example: ```ruby Post.where('id > 10').limit(20).order('id asc').except(:order) @@ -688,30 +698,24 @@ The SQL that would be executed: ```sql SELECT * FROM posts WHERE id > 10 LIMIT 20 -# Original query without `except` +# Original query without `unscope` SELECT * FROM posts WHERE id > 10 ORDER BY id asc LIMIT 20 ``` -### `unscope` - -The `except` method does not work when the relation is merged. For example: - -```ruby -Post.comments.except(:order) -``` - -will still have an order if the order comes from a default scope on Comment. In order to remove all ordering, even from relations which are merged in, use unscope as follows: +You can additionally unscope specific where clauses. For example: ```ruby -Post.order('id DESC').limit(20).unscope(:order) = Post.limit(20) -Post.order('id DESC').limit(20).unscope(:order, :limit) = Post.all +Post.where(id: 10, trashed: false).unscope(where: :id) +# SELECT "posts".* FROM "posts" WHERE trashed = 0 ``` -You can additionally unscope specific where clauses. For example: +A relation which has used `unscope` will affect any relation it is +merged in to: ```ruby -Post.where(:id => 10).limit(1).unscope(where: :id, :limit).order('id DESC') = Post.order('id DESC') +Post.order('id asc').merge(Post.unscope(:order)) +# SELECT "posts".* FROM "posts" ``` ### `only` @@ -786,6 +790,32 @@ SELECT * FROM clients WHERE orders_count > 10 ORDER BY clients.id DESC This method accepts **no** arguments. +### `rewhere` + +The `rewhere` method overrides an existing, named where condition. For example: + +```ruby +Post.where(trashed: true).rewhere(trashed: false) +``` + +The SQL that would be executed: + +```sql +SELECT * FROM posts WHERE `trashed` = 0 +``` + +In case the `rewhere` clause is not used, + +```ruby +Post.where(trashed: true).where(trashed: false) +``` + +the SQL executed would be: + +```sql +SELECT * FROM posts WHERE `trashed` = 1 AND `trashed` = 0 +``` + Null Relation ------------- @@ -931,9 +961,9 @@ SELECT clients.* FROM clients LEFT OUTER JOIN addresses ON addresses.client_id = WARNING: This method only works with `INNER JOIN`. -Active Record lets you use the names of the [associations](association_basics.html) defined on the model as a shortcut for specifying `JOIN` clause for those associations when using the `joins` method. +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. -For example, consider the following `Category`, `Post`, `Comments` and `Guest` models: +For example, consider the following `Category`, `Post`, `Comment`, `Guest` and `Tag` models: ```ruby class Category < ActiveRecord::Base @@ -1012,7 +1042,7 @@ Or, in English: "return all posts that have a comment made by a guest." #### Joining Nested Associations (Multiple Level) ```ruby -Category.joins(posts: [{comments: :guest}, :tags]) +Category.joins(posts: [{ comments: :guest }, :tags]) ``` This produces: @@ -1038,7 +1068,7 @@ An alternative and cleaner syntax is to nest the hash conditions: ```ruby time_range = (Time.now.midnight - 1.day)..Time.now.midnight -Client.joins(:orders).where(orders: {created_at: time_range}) +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. @@ -1099,7 +1129,7 @@ This loads all the posts and the associated category and comments for each post. #### Nested Associations Hash ```ruby -Category.includes(posts: [{comments: :guest}, :tags]).find(1) +Category.includes(posts: [{ comments: :guest }, :tags]).find(1) ``` This will find the category with id 1 and eager load all of the associated posts, the associated posts' tags and comments, and every comment's guest association. @@ -1179,7 +1209,7 @@ class Post < ActiveRecord::Base end ``` -This may then be called using this: +Call the scope as if it were a class method: ```ruby Post.created_before(Time.zone.now) @@ -1201,6 +1231,35 @@ Using a class method is the preferred way to accept arguments for scopes. These category.posts.created_before(time) ``` +### Applying a default scope + +If we wish for a scope to be applied across all queries to the model we can use the +`default_scope` method within the model itself. + +```ruby +class Client < ActiveRecord::Base + default_scope { where("removed_at IS NULL") } +end +``` + +When queries are executed on this model, the SQL query will now look something like +this: + +```sql +SELECT * FROM clients WHERE removed_at IS NULL +``` + +If you need to do more complex things with a default scope, you can alternatively +define it as a class method: + +```ruby +class Client < ActiveRecord::Base + def self.default_scope + # Should return an ActiveRecord::Relation. + end +end +``` + ### Merging of scopes Just like `where` clauses scopes are merged using `AND` conditions. @@ -1212,26 +1271,26 @@ class User < ActiveRecord::Base end User.active.inactive -# => SELECT "users".* FROM "users" WHERE "users"."state" = 'active' AND "users"."state" = 'inactive' +# SELECT "users".* FROM "users" WHERE "users"."state" = 'active' AND "users"."state" = 'inactive' ``` We can mix and match `scope` and `where` conditions and the final sql -will have all conditions joined with `AND` . +will have all conditions joined with `AND`. ```ruby User.active.where(state: 'finished') -# => SELECT "users".* FROM "users" WHERE "users"."state" = 'active' AND "users"."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 -be used . +be used. ```ruby User.active.merge(User.inactive) -# => SELECT "users".* FROM "users" WHERE "users"."state" = 'inactive' +# SELECT "users".* FROM "users" WHERE "users"."state" = 'inactive' ``` -One important caveat is that `default_scope` will be overridden by +One important caveat is that `default_scope` will be prepended in `scope` and `where` conditions. ```ruby @@ -1242,48 +1301,18 @@ class User < ActiveRecord::Base end User.all -# => SELECT "users".* FROM "users" WHERE "users"."state" = 'pending' +# SELECT "users".* FROM "users" WHERE "users"."state" = 'pending' User.active -# => SELECT "users".* FROM "users" WHERE "users"."state" = 'active' +# SELECT "users".* FROM "users" WHERE "users"."state" = 'pending' AND "users"."state" = 'active' User.where(state: 'inactive') -# => SELECT "users".* FROM "users" WHERE "users"."state" = 'inactive' +# SELECT "users".* FROM "users" WHERE "users"."state" = 'pending' AND "users"."state" = 'inactive' ``` -As you can see above the `default_scope` is being overridden by both +As you can see above the `default_scope` is being merged in both `scope` and `where` conditions. - -### Applying a default scope - -If we wish for a scope to be applied across all queries to the model we can use the -`default_scope` method within the model itself. - -```ruby -class Client < ActiveRecord::Base - default_scope { where("removed_at IS NULL") } -end -``` - -When queries are executed on this model, the SQL query will now look something like -this: - -```sql -SELECT * FROM clients WHERE removed_at IS NULL -``` - -If you need to do more complex things with a default scope, you can alternatively -define it as a class method: - -```ruby -class Client < ActiveRecord::Base - def self.default_scope - # Should return an ActiveRecord::Relation. - end -end -``` - ### Removing All Scoping If we wish to remove scoping for any reason we can use the `unscoped` method. This is @@ -1291,7 +1320,7 @@ especially useful if a `default_scope` is specified in the model and should not applied for this particular query. ```ruby -Client.unscoped.all +Client.unscoped.load ``` This method removes all scoping and will do a normal query on the table. @@ -1308,11 +1337,6 @@ Client.unscoped { Dynamic Finders --------------- -NOTE: 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 - For every field (also known as an attribute) you define in your table, Active Record provides a finder method. If you have a field called `first_name` on your `Client` model for example, you get `find_by_first_name` for free from Active Record. If you have a `locked` field on the `Client` model, you also get `find_by_locked` and methods. You can specify an exclamation point (`!`) on the end of the dynamic finders to get them to raise an `ActiveRecord::RecordNotFound` error if they do not return any records, like `Client.find_by_name!("Ryan")` @@ -1322,6 +1346,11 @@ If you want to find both by name and locked, you can chain these finders togethe 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` @@ -1348,7 +1377,7 @@ COMMIT The new record might not be saved to the database; that depends on whether validations passed or not (just like `create`). -Suppose we want to set the 'locked' attribute to true if we're +Suppose we want to set the 'locked' attribute to `false` if we're creating a new record, but we don't want to include it in the query. So we want to find the client named "Andy", or if that client doesn't exist, create a client named "Andy" which is not locked. @@ -1425,7 +1454,7 @@ If you'd like to use your own SQL to find records in a table you can use `find_b ```ruby Client.find_by_sql("SELECT * FROM clients INNER JOIN orders ON clients.id = orders.client_id - ORDER clients.created_at desc") + ORDER BY clients.created_at desc") ``` `find_by_sql` provides you with a simple way of making custom calls to the database and retrieving instantiated objects. @@ -1456,7 +1485,7 @@ Client.pluck(:id, :name) # => [[1, 'David'], [2, 'Jeremy'], [3, 'Jose']] ``` -`pluck` makes it possible to replace code like +`pluck` makes it possible to replace code like: ```ruby Client.select(:id).map { |c| c.id } @@ -1466,7 +1495,7 @@ Client.select(:id).map(&:id) Client.select(:id, :name).map { |c| [c.id, c.name] } ``` -with +with: ```ruby Client.pluck(:id) @@ -1474,6 +1503,37 @@ Client.pluck(:id) Client.pluck(:id, :name) ``` +Unlike `select`, `pluck` directly converts a database result into a Ruby `Array`, +without constructing `ActiveRecord` objects. This can mean better performance for +a large or often-running query. However, any model method overrides will +not be available. For example: + +```ruby +class Client < ActiveRecord::Base + def name + "I am #{super}" + end +end + +Client.select(:name).map &:name +# => ["I am David", "I am Jeremy", "I am Jose"] + +Client.pluck(:name) +# => ["David", "Jeremy", "Jose"] +``` + +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: + +```ruby +Client.pluck(:name).limit(1) +# => NoMethodError: undefined method `limit' for #<Array:0x007ff34d3ad6d8> + +Client.limit(1).pluck(:name) +# => ["David"] +``` + ### `ids` `ids` can be used to pluck all the IDs for the relation using the table's primary key. @@ -1495,18 +1555,21 @@ Person.ids Existence of Objects -------------------- -If you simply want to check for the existence of the object there's a method called `exists?`. This method will query the database using the same query as `find`, but instead of returning an object or collection of objects it will return either `true` or `false`. +If you simply want to check for the existence of the object there's a method called `exists?`. +This method will query the database using the same query as `find`, but instead of returning an +object or collection of objects it will return either `true` or `false`. ```ruby Client.exists?(1) ``` -The `exists?` method also takes multiple ids, but the catch is that it will return true if any one of those records exists. +The `exists?` method also takes multiple values, but the catch is that it will return `true` if any +one of those records exists. ```ruby -Client.exists?(1,2,3) +Client.exists?(id: [1,2,3]) # or -Client.exists?([1,2,3]) +Client.exists?(name: ['John', 'Sergei']) ``` It's even possible to use `exists?` without any arguments on a model or a relation. @@ -1515,7 +1578,8 @@ It's even possible to use `exists?` without any arguments on a model or a relati Client.where(first_name: 'Ryan').exists? ``` -The above returns `true` if there is at least one client with the `first_name` 'Ryan' and `false` otherwise. +The above returns `true` if there is at least one client with the `first_name` 'Ryan' and `false` +otherwise. ```ruby Client.exists? @@ -1565,7 +1629,7 @@ Client.where(first_name: 'Ryan').count You can also use various finder methods on a relation for performing complex calculations: ```ruby -Client.includes("orders").where(first_name: 'Ryan', orders: {status: 'received'}).count +Client.includes("orders").where(first_name: 'Ryan', orders: { status: 'received' }).count ``` Which will execute: |