Learn SQL With Rails 4 - Two Table Queries

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)