diff options
Diffstat (limited to 'guides/source/active_record_querying.md')
| -rw-r--r-- | guides/source/active_record_querying.md | 168 |
1 files changed, 84 insertions, 84 deletions
diff --git a/guides/source/active_record_querying.md b/guides/source/active_record_querying.md index 88ba7f25ca..4b3d95a3e1 100644 --- a/guides/source/active_record_querying.md +++ b/guides/source/active_record_querying.md @@ -472,8 +472,8 @@ Client.where('locked' => true) In the case of a belongs_to relationship, an association key can be used to specify the model if an Active Record object is used as the value. This method works with polymorphic relationships as well. ```ruby -Post.where(author: author) -Author.joins(:posts).where(posts: { author: author }) +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)`. @@ -511,7 +511,7 @@ SELECT * FROM clients WHERE (clients.orders_count IN (1,3,5)) `NOT` SQL queries can be built by `where.not`. ```ruby -Post.where.not(author: author) +Article.where.not(author: author) ``` In other words, this query can be generated by calling `where` with no argument, then immediately chain with `not` passing `where` conditions. @@ -690,32 +690,32 @@ Overriding Conditions 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) +Article.where('id > 10').limit(20).order('id asc').except(:order) ``` The SQL that would be executed: ```sql -SELECT * FROM posts WHERE id > 10 LIMIT 20 +SELECT * FROM articles WHERE id > 10 LIMIT 20 # Original query without `unscope` -SELECT * FROM posts WHERE id > 10 ORDER BY id asc LIMIT 20 +SELECT * FROM articles WHERE id > 10 ORDER BY id asc LIMIT 20 ``` You can additionally unscope specific where clauses. For example: ```ruby -Post.where(id: 10, trashed: false).unscope(where: :id) -# SELECT "posts".* FROM "posts" WHERE trashed = 0 +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: ```ruby -Post.order('id asc').merge(Post.unscope(:order)) -# SELECT "posts".* FROM "posts" +Article.order('id asc').merge(Article.unscope(:order)) +# SELECT "articles".* FROM "articles" ``` ### `only` @@ -723,16 +723,16 @@ Post.order('id asc').merge(Post.unscope(:order)) You can also override conditions using the `only` method. For example: ```ruby -Post.where('id > 10').limit(20).order('id desc').only(:order, :where) +Article.where('id > 10').limit(20).order('id desc').only(:order, :where) ``` The SQL that would be executed: ```sql -SELECT * FROM posts WHERE id > 10 ORDER BY id DESC +SELECT * FROM articles WHERE id > 10 ORDER BY id DESC # Original query without `only` -SELECT "posts".* FROM "posts" WHERE (id > 10) ORDER BY id desc LIMIT 20 +SELECT "articles".* FROM "articles" WHERE (id > 10) ORDER BY id desc LIMIT 20 ``` @@ -741,25 +741,25 @@ SELECT "posts".* FROM "posts" WHERE (id > 10) ORDER BY id desc LIMIT 20 The `reorder` method overrides the default scope order. For example: ```ruby -class Post < ActiveRecord::Base +class Article < ActiveRecord::Base .. .. has_many :comments, -> { order('posted_at DESC') } end -Post.find(10).comments.reorder('name') +Article.find(10).comments.reorder('name') ``` The SQL that would be executed: ```sql -SELECT * FROM comments WHERE post_id = 10 ORDER BY name +SELECT * FROM articles WHERE id = 10 ORDER BY name ``` In case the `reorder` clause is not used, the SQL executed would be: ```sql -SELECT * FROM comments WHERE post_id = 10 ORDER BY posted_at DESC +SELECT * FROM articles WHERE id = 10 ORDER BY posted_at DESC ``` ### `reverse_order` @@ -795,25 +795,25 @@ This method accepts **no** arguments. The `rewhere` method overrides an existing, named where condition. For example: ```ruby -Post.where(trashed: true).rewhere(trashed: false) +Article.where(trashed: true).rewhere(trashed: false) ``` The SQL that would be executed: ```sql -SELECT * FROM posts WHERE `trashed` = 0 +SELECT * FROM articles WHERE `trashed` = 0 ``` In case the `rewhere` clause is not used, ```ruby -Post.where(trashed: true).where(trashed: false) +Article.where(trashed: true).where(trashed: false) ``` the SQL executed would be: ```sql -SELECT * FROM posts WHERE `trashed` = 1 AND `trashed` = 0 +SELECT * FROM articles WHERE `trashed` = 1 AND `trashed` = 0 ``` Null Relation @@ -822,21 +822,21 @@ Null Relation The `none` method returns a chainable relation with no records. Any subsequent conditions chained to the returned relation will continue generating empty relations. This is useful in scenarios where you need a chainable response to a method or a scope that could return zero results. ```ruby -Post.none # returns an empty Relation and fires no queries. +Article.none # returns an empty Relation and fires no queries. ``` ```ruby -# The visible_posts method below is expected to return a Relation. -@posts = current_user.visible_posts.where(name: params[:name]) +# The visible_articles method below is expected to return a Relation. +@articles = current_user.visible_articles.where(name: params[:name]) -def visible_posts +def visible_articles case role when 'Country Manager' - Post.where(country: country) + Article.where(country: country) when 'Reviewer' - Post.published + Article.published when 'Bad User' - Post.none # => returning [] or nil breaks the caller code in this case + Article.none # => returning [] or nil breaks the caller code in this case end end ``` @@ -963,21 +963,21 @@ 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` clauses for those associations when using the `joins` method. -For example, consider the following `Category`, `Post`, `Comment`, `Guest` and `Tag` models: +For example, consider the following `Category`, `Article`, `Comment`, `Guest` and `Tag` models: ```ruby class Category < ActiveRecord::Base - has_many :posts + has_many :articles end -class Post < ActiveRecord::Base +class Article < ActiveRecord::Base belongs_to :category has_many :comments has_many :tags end class Comment < ActiveRecord::Base - belongs_to :post + belongs_to :article has_one :guest end @@ -986,7 +986,7 @@ class Guest < ActiveRecord::Base end class Tag < ActiveRecord::Base - belongs_to :post + belongs_to :article end ``` @@ -995,64 +995,64 @@ Now all of the following will produce the expected join queries using `INNER JOI #### Joining a Single Association ```ruby -Category.joins(:posts) +Category.joins(:articles) ``` This produces: ```sql SELECT categories.* FROM categories - INNER JOIN posts ON posts.category_id = categories.id + INNER JOIN articles ON articles.category_id = categories.id ``` -Or, in English: "return a Category object for all categories with posts". Note that you will see duplicate categories if more than one post has the same category. If you want unique categories, you can use `Category.joins(:posts).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).uniq`. #### Joining Multiple Associations ```ruby -Post.joins(:category, :comments) +Article.joins(:category, :comments) ``` This produces: ```sql -SELECT posts.* FROM posts - INNER JOIN categories ON posts.category_id = categories.id - INNER JOIN comments ON comments.post_id = posts.id +SELECT articles.* FROM articles + INNER JOIN categories ON articles.category_id = categories.id + INNER JOIN comments ON comments.article_id = articles.id ``` -Or, in English: "return all posts that have a category and at least one comment". Note again that posts with multiple comments will show up multiple times. +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) ```ruby -Post.joins(comments: :guest) +Article.joins(comments: :guest) ``` This produces: ```sql -SELECT posts.* FROM posts - INNER JOIN comments ON comments.post_id = posts.id +SELECT articles.* FROM articles + INNER JOIN comments ON comments.article_id = articles.id INNER JOIN guests ON guests.comment_id = comments.id ``` -Or, in English: "return all posts that have a comment made by a guest." +Or, in English: "return all articles that have a comment made by a guest." #### Joining Nested Associations (Multiple Level) ```ruby -Category.joins(posts: [{ comments: :guest }, :tags]) +Category.joins(articles: [{ comments: :guest }, :tags]) ``` This produces: ```sql SELECT categories.* FROM categories - INNER JOIN posts ON posts.category_id = categories.id - INNER JOIN comments ON comments.post_id = posts.id + INNER JOIN articles ON articles.category_id = categories.id + INNER JOIN comments ON comments.article_id = articles.id INNER JOIN guests ON guests.comment_id = comments.id - INNER JOIN tags ON tags.post_id = posts.id + INNER JOIN tags ON tags.article_id = articles.id ``` ### Specifying Conditions on the Joined Tables @@ -1121,18 +1121,18 @@ Active Record lets you eager load any number of associations with a single `Mode #### Array of Multiple Associations ```ruby -Post.includes(:category, :comments) +Article.includes(:category, :comments) ``` -This loads all the posts and the associated category and comments for each post. +This loads all the articles and the associated category and comments for each article. #### Nested Associations Hash ```ruby -Category.includes(posts: [{ comments: :guest }, :tags]).find(1) +Category.includes(articles: [{ 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. +This will find the category with id 1 and eager load all of the associated articles, the associated articles' tags and comments, and every comment's guest association. ### Specifying Conditions on Eager Loaded Associations @@ -1141,18 +1141,18 @@ Even though Active Record lets you specify conditions on the eager loaded associ However if you must do this, you may use `where` as you would normally. ```ruby -Post.includes(:comments).where("comments.visible" => true) +Article.includes(:comments).where("comments.visible" => true) ``` This would generate a query which contains a `LEFT OUTER JOIN` whereas the `joins` method would generate one using the `INNER JOIN` function instead. ```ruby - SELECT "posts"."id" AS t0_r0, ... "comments"."updated_at" AS t1_r5 FROM "posts" LEFT OUTER JOIN "comments" ON "comments"."post_id" = "posts"."id" WHERE (comments.visible = 1) + SELECT "articles"."id" AS t0_r0, ... "comments"."updated_at" AS t1_r5 FROM "articles" LEFT OUTER JOIN "comments" ON "comments"."article_id" = "articles"."id" WHERE (comments.visible = 1) ``` If there was no `where` condition, this would generate the normal set of two queries. -If, in the case of this `includes` query, there were no comments for any posts, all the posts would still be loaded. By using `joins` (an INNER JOIN), the join conditions **must** match, otherwise no records will be returned. +If, in the case of this `includes` query, there were no comments for any articles, all the articles would still be loaded. By using `joins` (an INNER JOIN), the join conditions **must** match, otherwise no records will be returned. Scopes ------ @@ -1162,7 +1162,7 @@ Scoping allows you to specify commonly-used queries which can be referenced as m 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: ```ruby -class Post < ActiveRecord::Base +class Article < ActiveRecord::Base scope :published, -> { where(published: true) } end ``` @@ -1170,7 +1170,7 @@ end This is exactly the same as defining a class method, and which you use is a matter of personal preference: ```ruby -class Post < ActiveRecord::Base +class Article < ActiveRecord::Base def self.published where(published: true) end @@ -1180,7 +1180,7 @@ end Scopes are also chainable within scopes: ```ruby -class Post < ActiveRecord::Base +class Article < ActiveRecord::Base scope :published, -> { where(published: true) } scope :published_and_commented, -> { published.where("comments_count > 0") } end @@ -1189,14 +1189,14 @@ end To call this `published` scope we can call it on either the class: ```ruby -Post.published # => [published posts] +Article.published # => [published articles] ``` -Or on an association consisting of `Post` objects: +Or on an association consisting of `Article` objects: ```ruby category = Category.first -category.posts.published # => [published posts belonging to this category] +category.articles.published # => [published articles belonging to this category] ``` ### Passing in arguments @@ -1204,7 +1204,7 @@ category.posts.published # => [published posts belonging to this category] Your scope can take arguments: ```ruby -class Post < ActiveRecord::Base +class Article < ActiveRecord::Base scope :created_before, ->(time) { where("created_at < ?", time) } end ``` @@ -1212,13 +1212,13 @@ end Call the scope as if it were a class method: ```ruby -Post.created_before(Time.zone.now) +Article.created_before(Time.zone.now) ``` However, this is just duplicating the functionality that would be provided to you by a class method. ```ruby -class Post < ActiveRecord::Base +class Article < ActiveRecord::Base def self.created_before(time) where("created_at < ?", time) end @@ -1228,7 +1228,7 @@ end Using a class method is the preferred way to accept arguments for scopes. These methods will still be accessible on the association objects: ```ruby -category.posts.created_before(time) +category.articles.created_before(time) ``` ### Applying a default scope @@ -1591,20 +1591,20 @@ You can also use `any?` and `many?` to check for existence on a model or relatio ```ruby # via a model -Post.any? -Post.many? +Article.any? +Article.many? # via a named scope -Post.recent.any? -Post.recent.many? +Article.recent.any? +Article.recent.many? # via a relation -Post.where(published: true).any? -Post.where(published: true).many? +Article.where(published: true).any? +Article.where(published: true).many? # via an association -Post.first.categories.any? -Post.first.categories.many? +Article.first.categories.any? +Article.first.categories.many? ``` Calculations @@ -1694,18 +1694,18 @@ Running EXPLAIN You can run EXPLAIN on the queries triggered by relations. For example, ```ruby -User.where(id: 1).joins(:posts).explain +User.where(id: 1).joins(:articles).explain ``` may yield ``` -EXPLAIN for: SELECT `users`.* FROM `users` INNER JOIN `posts` ON `posts`.`user_id` = `users`.`id` WHERE `users`.`id` = 1 +EXPLAIN for: SELECT `users`.* FROM `users` INNER JOIN `articles` ON `articles`.`user_id` = `users`.`id` WHERE `users`.`id` = 1 +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+ | 1 | SIMPLE | users | const | PRIMARY | PRIMARY | 4 | const | 1 | | -| 1 | SIMPLE | posts | ALL | NULL | NULL | NULL | NULL | 1 | Using where | +| 1 | SIMPLE | articles | ALL | NULL | NULL | NULL | NULL | 1 | Using where | +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+ 2 rows in set (0.00 sec) ``` @@ -1716,15 +1716,15 @@ Active Record performs a pretty printing that emulates the one of the database shells. So, the same query running with the PostgreSQL adapter would yield instead ``` -EXPLAIN for: SELECT "users".* FROM "users" INNER JOIN "posts" ON "posts"."user_id" = "users"."id" WHERE "users"."id" = 1 +EXPLAIN for: SELECT "users".* FROM "users" INNER JOIN "articles" ON "articles"."user_id" = "users"."id" WHERE "users"."id" = 1 QUERY PLAN ------------------------------------------------------------------------------ Nested Loop Left Join (cost=0.00..37.24 rows=8 width=0) - Join Filter: (posts.user_id = users.id) + Join Filter: (articles.user_id = users.id) -> Index Scan using users_pkey on users (cost=0.00..8.27 rows=1 width=4) Index Cond: (id = 1) - -> Seq Scan on posts (cost=0.00..28.88 rows=8 width=4) - Filter: (posts.user_id = 1) + -> Seq Scan on articles (cost=0.00..28.88 rows=8 width=4) + Filter: (articles.user_id = 1) (6 rows) ``` @@ -1733,7 +1733,7 @@ may need the results of previous ones. Because of that, `explain` actually executes the query, and then asks for the query plans. For example, ```ruby -User.where(id: 1).includes(:posts).explain +User.where(id: 1).includes(:articles).explain ``` yields @@ -1747,11 +1747,11 @@ EXPLAIN for: SELECT `users`.* FROM `users` WHERE `users`.`id` = 1 +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ 1 row in set (0.00 sec) -EXPLAIN for: SELECT `posts`.* FROM `posts` WHERE `posts`.`user_id` IN (1) +EXPLAIN for: SELECT `articles`.* FROM `articles` WHERE `articles`.`user_id` IN (1) +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ -| 1 | SIMPLE | posts | ALL | NULL | NULL | NULL | NULL | 1 | Using where | +| 1 | SIMPLE | articles | ALL | NULL | NULL | NULL | NULL | 1 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) ``` |