Rails/ActiveRecord SQL Queries for Multiple Tables

In the previous post, we created a simple CRUD MVC for a Post model and this blog entry will extend the example, by adding an associated comments model. Start by creating a Comment model that belongs to a Post and has a body attribute.

$ rails g model comment body:text post_id:integer
$ rake db:migrate

Associate the Post and Comment models.

# models/post.rb
class Post < ActiveRecord::Base
  attr_accessible :body, :title
  has_many :comments
end

# models/comment.rb
class Comment < ActiveRecord::Base
  attr_accessible :body, :post_id
  belongs_to :post
end

Create a rake task that will create 10 comments for every post.

# lib/tasks/create_comments.rake
desc "Creates 10 comments per post"
task :create_comments => :environment do
  Post.all.each do |post|
    10.times do |counter|
      post.comments.create(body: "Comment-#{counter + 1}")
    end
  end
end
$ rake create_comments

Write a SQL query that will fetch all comments for the first post.

SELECT "comments".* FROM "comments" WHERE "comments"."post_id" = 1

This query does not necessitate a join table because we are simply pulling all comment records where the post_id is equal to 1 (the post_id column is in the comments table).

Similarly, join tables are not required to fetch the post record for the comment with and id of 60. We can fetch the comment record with an id of 60, find that the associated post has an id of 6 and run the following query:

SELECT "posts".* FROM "posts" WHERE "posts"."id" = 6 LIMIT 1

Create a rake task that randomly populates the body of every Post with either “snake”, “rat”, “cat”, “dog”, “penguin”, or “goose” and also randomly populates the body of every Comment with an id that id divisible by 14 with ones of these strings as well.

desc "Repopulates the body of comments and posts"
task :repopulate_body => :environment do
  values = %w{snake rat cat dog penguin goose}
  Post.all.each do |post|
    post.update_attribute(:body, values.sample)
  end
  comments = Comment.select{|c| c.id % 14 == 0}
  comments.each do |comment|
    comment.update_attribute(:body, values.sample)
  end
end

Write the SQL to fetch all post records that have the same body as one of their comments.

SELECT "posts".* FROM "posts" INNER JOIN "comments" ON "comments"."post_id" = "posts"."id" WHERE (posts.body = comments.body)

Write the ActiveRecord query that generates the SQL query above.

>> Post.joins(:comments).where("posts.body = comments.body")

In future posts we will investigate the SQL queries that are generated by has_and_belongs_to_many and has_many_through database relationships as well as advanced SQL features and the ActiveRecord Arel counterparts.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s