Learn SQL With Rails 4 - Join Query

Question Click to View Answer

Create a rails application called join_table_queries.

 
$ rails new join_table_queries

Generate a magazine model with a name attribute. Generate a reader model with a name attribute. Generate a subscription join model. Create the tables in the database.

 
$ rails g model magazine name
$ rails g model reader name
$ rails g model subscription magazine_id:integer reader_id:integer
$ rake db:migrate

Associate the models with a has_many :through association.

 
# models/magazine.rb
class Magazine < ActiveRecord::Base
  has_many :subscriptions
  has_many :readers, through: :subscriptions
end

# models/reader.rb
class Reader < ActiveRecord::Base
  has_many :subscriptions
  has_many :magazines, through: :subscriptions
end

# models/subscription.rb
class Subscription < ActiveRecord::Base
  belongs_to :reader
  belongs_to :magazine
end

Seed the database with the following data:

# db/seeds.rb
vogue = Magazine.create!(:name => "Vogue")
gq = Magazine.create!(:name => "GQ")
bob = Reader.create!(:name => "Bob")
frank = Reader.create!(:name => "Frank")
bob.subscriptions.create!(magazine: vogue)
bob.subscriptions.create!(magazine: gq)
frank.subscriptions.create!(magazine: gq)

$ rake db:seed

Write the SQL queries that are generated by the following code:

reader = Reader.first
reader.subscriptions

SELECT "readers".* FROM "readers" ORDER BY "readers"."id" ASC LIMIT 1;
SELECT "subscriptions".* FROM "subscriptions" WHERE "subscriptions"."reader_id" = 1;

Write the SQL queries that are generated by the following code:

reader = Reader.first
reader.magazines

SELECT "readers".* FROM "readers" ORDER BY "readers"."id" ASC LIMIT 1;
SELECT "magazines".* FROM "magazines" INNER JOIN "subscriptions" ON "magazines"."id" = "subscriptions"."magazine_id" WHERE "subscriptions"."reader_id" = 1;

Write the SQL queries that are generate by the following code:

sub = Subscription.first
sub.magazine

SELECT "subscriptions".* FROM "subscriptions" ORDER BY "subscriptions"."id" ASC LIMIT 1;
SELECT "magazines".* FROM "magazines" WHERE "magazines"."id" = 1 LIMIT 1;

Write the SQL queries that are generate by the following code:

sub = Subscription.first
sub.reader

SELECT "subscriptions".* FROM "subscriptions" ORDER BY "subscriptions"."id" ASC LIMIT 1;
SELECT "readers".* FROM "readers"  WHERE "readers"."id" = 1 LIMIT 1;

Write the SQL queries that are generate by the following code:

magazine = Magazine.first
magazine.readers

SELECT "magazines".* FROM "magazines" ORDER BY "magazines"."id" ASC LIMIT 1;
SELECT "readers".* FROM "readers" INNER JOIN "subscriptions" ON "readers"."id" = "subscriptions"."reader_id" WHERE "subscriptions"."magazine_id" = 1;

Write the SQL queries that are generated by the following code:

magazine = Magazine.first
magazine.subscriptions

SELECT "magazines".* FROM "magazines" ORDER BY "magazines"."id" ASC LIMIT 1;
SELECT "subscriptions".* FROM "subscriptions" WHERE "subscriptions"."magazine_id" = 1;