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:

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:

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.

### Like this:

Like Loading...

*Related*