Rails SQL Queries for has_and_belongs_to_many database relationships

This post demonstrates the SQL queries that Rails/ActiveRecord generates for models that are associated with a has_and_belongs_to_many relationship. Suppose there is a Student model with name and age attributes and a Course model with name and credits attributes. The Student and Course models are joined by a courses_students table. The schema is defined as follows:

create_table "courses", :force => true do |t|
  t.string   "name"
  t.float    "credits"
  t.datetime "created_at", :null => false
  t.datetime "updated_at", :null => false
end

create_table "courses_students", :id => false, :force => true do |t|
  t.integer "student_id"
  t.integer "course_id"
end

create_table "students", :force => true do |t|
  t.string   "name"
  t.integer  "age"
  t.datetime "created_at", :null => false
  t.datetime "updated_at", :null => false
end

In Rails, it is common to use course.students to return all students associated with a course. If the course id is 1, the SQL query that is generated by course.students is as follows:

SELECT "students".* FROM "students" INNER JOIN "courses_students" ON "students"."id" = "courses_students"."student_id" WHERE "courses_students"."course_id" = 1

This query joins the students and courses_students table to make the course_id accessible. Let’s us an example to demonstrate the mechanics of the INNER JOIN.

The students, courses, and courses_students tables are populated with the following data:
Screen Shot 2013-07-14 at 7.45.55 PM

Screen Shot 2013-07-14 at 7.46.56 PM

Screen Shot 2013-07-14 at 7.47.09 PM

We want to find all the students that are enrolled in the course that has an id of 1 (English). By joining the students and courses_students table, we have access to the students and the course_ids. From the join table, we can fetch all students with a course_id of 1. Here is the SQL statement:

SELECT "students".*, "courses_students".course_id FROM "students" INNER JOIN "courses_students" ON "students"."id" = "courses_students"."student_id";

The SQL statement generates this join table:
Screen Shot 2013-07-14 at 8.07.22 PM

From the join table, we simply fetch all students with a course_id equal to 1 to locate all the students in the English class.

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