has_many :through with Single Table inheritance

rubyonrails-talk
1

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

2

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

3

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

4

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