has_many :through with Single Table inheritance

I have the following model structure setup.

class User < ActiveRecord::Base
end

class Parent < User
   has_many :relationships
   has_many :children,
              :class_name => "Student",
              :through => :relationships,
              :conditions => "related_as = 'parent'"
end

class Student < User
  has_many :relationships
  has_many :parents,
              :through => :relationships,
              :conditions => "related_as = 'child'"
end

class Relationship < ActiveRecord::Base
  belongs_to :parent, :foreign_key => :user_id
  belongs_to :student, :foreign_key => :user_id
end

class CreateRelationships < ActiveRecord::Migration
  def self.up
    create_table :relationships do |t|
      t.integer :user_id, :null => false
      t.integer :related_user_id, :null => false
      t.string :status, :null => false
      t.string :related_as, :limit => 100, :null => false #
e.g. 'parent' or 'child' or 'friend'
      t.timestamps
    end
  end

  def self.down
    drop_table :relationships
  end
end

I have the following model structure setup.

class User < ActiveRecord::Base
end

class Parent < User
has_many :relationships
has_many :children,
:class_name => "Student",
:through => :relationships,
:conditions => "related_as = 'parent'"
end

class Student < User
has_many :relationships
has_many :parents,
:through => :relationships,
:conditions => "related_as = 'child'"
end

class Relationship < ActiveRecord::Base
belongs_to :parent, :foreign_key => :user_id
belongs_to :student, :foreign_key => :user_id
end

This isn't going to do what you want - now you've got two belongs_to
associations that both want to use the 'user_id' column to store their
foreign key.

ActiveRecord::StatementInvalid: Mysql::Error: Unknown column
'relationships.student_id' in 'where clause': SELECT `users`.* FROM
`users` INNER JOIN `
relationships` ON `users`.id = `relationships`.user_id WHERE
((`relationships`.student_id = 144) AND ((related_as = 'child'))) AND
( (`users`.`type
` = 'Parent' ) )

The problem is in WHERE clause `relationships`.student_id - I don't
have a student_id in that model (trying to keep it generic) and
instead want to use the related_user_id column from relationships
table. I have played around with the :foreign_key attribute on
belongs_to setup for relationships table and the way I have it setup
now generates the first part of the SQL correctly `users`.id =
`relationships`.user_id .

How do I change the `relationships`.student_id to use
`relationships`.related_user_id instead? Or is there a better/
different way to do what I am trying to accomplish? Do I have to
resort to a Polymorphic setup here (quick, half hearted try there
gives some other issues that I need to look deeper into if I have to
go down that route)?

You're going to need to pass :foreign_key to the
has_many :relationships calls as well, as that's whats generating the
bad SQL (a standard has_many :relationships on Student will look for
relationships.student_id)

You'll probably want something more like this (not tested):

class Parent < User
   has_many :relationships, :foreign_key => 'user_id'
   has_many :children,
              :class_name => "Student",
              :through => :relationships,
              :conditions => "related_as = 'parent'",
              :source => :student
end
class Student < User
  has_many :relationships, :foreign_key => 'related_user_id'
  has_many :parents,
              :through => :relationships,
              :conditions => "related_as = 'child'",
end
class Relationship < ActiveRecord::Base
  belongs_to :parent, :foreign_key => :user_id
  belongs_to :student, :foreign_key => :related_user_id
end

Note that you *can* declare duplicate belongs_to on the same foreign
key if needed, so you could add:

belongs_to :husband, :foreign_key => 'user_id'
belongs_to :wife, :foreign_key => 'related_user_id'

The friend/friend case is messier - there are some good tutorials on
different ways to accomplish it with has_many :through.

--Matt Jones

Thanks Matt. I plan to store two records per relationship - one for
the person who makes the request and one for the person of whom the
request is made - may not be that useful for parent/child relationship
but will be very useful for friend/friend relationship. With that in
mind the user_id field will contain the parent's id for one of the
records and the child's id for the other record with the related_as
field reflecting the relationship - 'parent' in one case and 'child'
in another. With the way you have setup it doesn't look like I can do
that - you seem to assume one record per relationship - let me know if
I am misunderstanding. Not ideal from compactness perspective, but
this dual record entry becomes even more important when I have friend
requests and need to keep track of statuses/rejections/cancelations
etc - basing off of some examples I have seen.

I will Google around but if you know of some good examples for the
friend/friend case with has_many :through that you mention above,
could you please point me to them?

Thanks again for your help.

-S

I finally got it to work. Thanks Matt for pointing me in the right
direction. For people who stumble on this in the future, here is how I
set it up - hope it helps someone down the road.

class User < ActiveRecord::Base
end

class Parent < User
   has_many :relationships, :foreign_key => :user_id
   has_many :children,
              :through => :relationships,
              :class_name => "Student",
              :conditions => "related_as = 'parent'"
end

class Student < User
  has_many :relationships, :foreign_key => :user_id
  has_many :parents,
              :through => :relationships,
              :conditions => "related_as = 'child'"
end

class Relationship < ActiveRecord::Base
  belongs_to :parent, :foreign_key => :related_user_id
  belongs_to :child, :class_name => "Student", :foreign_key
=> :related_user_id
end