Rails - Apr 2006 - Join using :THROUGH - SQL is wrong even when using :SOURCE

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?

-- 
Posted via http://www.ruby-forum.com/.

Peter Bengtson 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 Susser
http://blog.hasmanythrough.com

-- 
Posted via http://www.ruby-forum.com/.