Question | Click to View Answer |
Create a rails application called two_table_queries. |
$ rails new two_table_queries
|
Create an Article model with a title attribute. |
$ rails g model Article title:string
|
Create a Comment model with a content attribute. An article has_many comments. Create the tables in the database. |
$ rails g model Comment content:text article_id:integer
$ rake db:migrate
|
Update the seed file to make 10 articles with titles that are a sequence of 10 random characters. For each article, create 10 comments with content like "comment 1 for article 1", "comment 2 for article 1", etc. |
# db/seeds.rb
def random_string(n)
('a'..'z').to_a.shuffle[0..n-1].join
end
10.times do
Article.create(title: random_string(8))
end
Article.all.each do |a|
10.times do |n|
str = "Comment: #{n + 1} for article #{a.id}"
Comment.create(content: str, article_id: a.id)
end
end
$ rake db:seed
|
Add code so the following line works: Article.first.comments |
# app/models/article.rb
class Article < ActiveRecord::Base
has_many :comments
end
|
Write the SQL that's generated by the following command: Article.first.comments
|
SELECT "articles".* FROM "articles"
ORDER BY "articles"."id" ASC LIMIT 1
SELECT "comments".* FROM "comments"
WHERE "comments"."article_id" = ? [["article_id", 1]]
|
Add code so the following line works: Comment.first.article
|
# app/models/comment.rb
class Comment < ActiveRecord::Base
belongs_to :article
end
|
Write the SQL that's generated by the following command: Comment.first.article
|
SELECT "comments".* FROM "comments"
ORDER BY "comments"."id" ASC LIMIT 1
SELECT "articles".* FROM "articles"
WHERE "articles"."id" = ? LIMIT 1 [["id", 1]]
|
Write the SQL that's generated by the last line of the following code: article = Article.last
article.comments.create(content: "boo")
|
INSERT INTO "comments" ("article_id", "content", "created_at", "updated_at") VALUES (?, ?, ?, ?) [["article_id", 10], ["content", "boo"], ["created_at", "2014-06-28 00:50:27.281805"], ["updated_at", "2014-06-28 00:50:27.281805"]]
|
Write the SQL that's generated by the last line of the following code: comment = Comment.last
comment.article.update_attributes(title: "woo hoo")
|
UPDATE "articles" SET "title" = ?, "updated_at" = ? WHERE "articles"."id" = 10 [["title", "woo hoo"], ["updated_at", "2014-06-28 00:55:05.358878"]]
|
What's the problem with the following code? # app/models/article.rb
def self.comment_contents(n)
articles = Article.first(n)
result = []
articles.each do |a|
a.comments.each do |c|
result << c.content
end
end
result
end
|
This example perfectly illustrates the n + 1 query problem. For an input size n, this method makes n + 1 queries, which is very inefficient. a.comments runs a new query for each loop iteration. |
Refactor the following method so it's more efficient. # app/models/article.rb
def self.comment_contents(n)
articles = Article.first(n)
result = []
articles.each do |a|
a.comments.each do |c|
result << c.content
end
end
result
end
|
Replace the first line of the method as follows: articles = Article.includes(:comments).first(n)
This refactored method only makes 2 queries regardless of the size of n, which solve the n + 1 problem. |
What SQL is generated by the following query? Article.includes(:comments).last(2)
|
SELECT "articles".* FROM "articles" ORDER BY "articles"."id" DESC LIMIT 2
SELECT "comments".* FROM "comments" WHERE "comments"."article_id" IN (10, 9)
|