Has_many :through with Single Table inheritance

Rails
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

I am trying to use the Relationship model to store many different
kinds of relationships like parent/child, friend/friend, husband/wife
etc.

With the above setup when I go to console I get the following

c = Student.find(144)
=>

c.parents
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)?

I am on 2.3.8.

Thanks for your help.
-S

2

On Jan 14, 7:49pm, skt [email protected] wrote:

   :conditions => "related_as = 'parent'"

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.

have a student_id in that model (trying to keep it generic) and
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 J.

3

On Jan 15, 8:34am, Matt J. [email protected] wrote:

I have the following model structure setup.
end
belongs_to :student, :foreign_key => :user_id

( (users.type How do I change the relationships`.student_id to use
You’ll probably want something more like this (not tested):
has_many :relationships, :foreign_key => ‘related_user_id’
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 withhas_many:through.

–Matt J.

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

On Jan 17, 9:30am, skt [email protected] wrote:

On Jan 14, 7:49pm,skt[email protected] wrote:

   :through=> :relationships,

class Relationship < ActiveRecord::Base
users INNER JOIN now generates the first part of the SQL correctlyusers`.id =
bad SQL (a standardhas_many:relationships on Student will look for
:source => :student
end
–Matt J.
I am misunderstanding. Not ideal from compactness perspective, but
-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