Join using :THROUGH - SQL is wrong even when using :SOURCE


#1

Here’s a join table for an educational institution:

create_table “student_appointments”, :force => true do |t|
t.column “supervisor_id”, :integer, :default => 0, :null => false
t.column “student_id”, :integer, :default => 0, :null => false
t.column “course_id”, :integer, :default => 0, :null => false
[other columns omitted for clarity]
end

It is meant to be created when a student is accepted as a participant of
a course and also records the student’s supervisor. Its model is defined
as:

class StudentAppointment < ActiveRecord::Base
belongs_to :student, :class_name => ‘User’, :foreign_key =>
‘student_id’
belongs_to :supervisor, :class_name => ‘User’, :foreign_key =>
‘supervisor_id’
belongs_to :course
end

As you can see, the student and supervisor properties both reference
User objects. Therefore, :foreign_key is used to differentiate between
them.

The User class definition goes like this:

class User < ActiveRecord::Base
has_many :student_appointments
has_many :courses, :through => :student_appointments
has_many :supervisors, :through => :student_appointments
has_many :students, :through => :student_appointments
[many lines deleted for brevity’s sake]
end

Now, this should enable us to do queries (or so I thought) like

u = User.find :first
u.courses

But Rails 1.1.1 explodes with the error message:

“Mysql::Error: Unknown column ‘student_appointments.user_id’ in ‘where
clause’: SELECT courses.* FROM courses INNER JOIN student_appointments
ON courses.id = student_appointments.course_id WHERE
(student_appointments.user_id = 1)”

Clearly, user_id should be student_id, as the :foreign_key declaration
in the StudentAppointment class states. But of course ActionModel has no
way of knowing this. There is no room to declare which column should be
used – student_id or supervisor_id – and ActionModel instead blindly
goes for user_id, the index column name derived from the class name.
Kaboom!

The documentation states that :foreign_key declarations are ignored when
:through is used. If this is meant in its widest and strictest sense, it
means that column names in the join table must be named exactly like the
tables they reference, which makes it impossible to join records from
the same table more than once.

If this is so, the 1.1 joins, while conceptually nice, are of limited
usefulness.

Adding a :source => :course to the User model doesn’t help, of course.
The crux of the matter lies in informing the ActionModel of which
foreign key should be used in the join table. Currently, I see no way of
doing this, and the only alternative I can think of is to define the
joins in separate tables: one associating a User and a Course together
(a “Student” object) and then associating this object with a Supervisor
object.

Am I correct?


#2

Peter B. wrote:

The documentation states that :foreign_key declarations are ignored when
:through is used. If this is meant in its widest and strictest sense, it
means that column names in the join table must be named exactly like the
tables they reference, which makes it impossible to join records from
the same table more than once.

If this is so, the 1.1 joins, while conceptually nice, are of limited
usefulness.

Adding a :source => :course to the User model doesn’t help, of course.
The crux of the matter lies in informing the ActionModel of which
foreign key should be used in the join table. Currently, I see no way of
doing this, and the only alternative I can think of is to define the
joins in separate tables: one associating a User and a Course together
(a “Student” object) and then associating this object with a Supervisor
object.

Am I correct?

Looks like you’re right. I think has_many :through needs to support
:association_foreign_key to make this work.
http://dev.rubyonrails.org/ticket/4289 flirted with doing that, but
settled on :source, however this was for controlling the other side of
the join. I think your example makes a case for needing
:association_foreign_key for the owner side too.


Josh S.
http://blog.hasmanythrough.com