05 Dec 2005Many To Many Rails Goodness
layout: post title: Many-to-many rails goodness with the through directive —- The has_many :through option will rock your socks off! (Or at least make things a little easier) Read on to understand why!
Rails has the ability to declare many-to-many relationships between ActiveRecord objects using the has_and_belongs_to_many macro. HABTM relationships as they are affectionately known, require a third join table that contains the keys of the two domain objects.
Consider the example where a Student can be enrolled in 1 or more Subjects and each Subject can have 1 or more Students. This would be modelled by the following domain classes.
With the sql DDL looking something like;
CREATE TABLE students ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(100) NOT NULL, ... PRIMARY KEY (id) ) ENGINE = InnoDB; CREATE TABLE subjects ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(100) NOT NULL, ... PRIMARY KEY (id) ) ENGINE = InnoDB; CREATE TABLE students_subjects ( student_id INT NOT NULL, subjects_id INT NOT NULL, FOREIGN KEY (student_id) REFERENCES students(id), FOREIGN KEY (subject_id) REFERENCES subjects(id) ) ENGINE = InnoDB;
At some point you may want to add in some information
about the HABTM relationship such as the year that the
student enrolled in the subject. This can be done using
push\_with\_attributes and adding an extra
‘year’ column to the SQL DDL.
Join tables with attributes tend to become ugly fast and it is rare that a few days don’t go by on the rails mailing list without someone asking for features that imply they are using join tables as a crutch for a missing domain object. Enrolment would be a good choice for the example above.
However if we introduce an Enrolment object, the naive approach of accessing the students of the subject (or vice versa) is extremely inefficient as you will first hit the enrolments table before loading from the students table (or conversly the subjects table). A more efficient way using hand crafted SQL would be the following
This pattern is likely to be duplicated across many domain objects.
Luckily David Heinemeier Hansson mentioned
:through option will be supported on the the
has\_many macro that allows you to replace the above code with;
Rails just keeps getting better and better!