Foreign Key to Foreign Key Relationships and Single Table Inheritance in Ruby on Rails

February 20th, 2008 by Carson Keith

ActiveRecord is great and efficient for simple normalized databases, but once you start tread off the beaten path ActiveRecord starts to lose a lot of its mojo. A few months ago I had a rather abstract database that utilized a number of models based on Single Table Inheritance (STI). Prior to refactoring our our database diagram looked something like an upside down untranslated Tokyo subway map. Fortunately single table inheritance saved our butt big time and we were able to knock off a number of confusing entities from the database and refactor in some common functionality. There was but one problem though. Before our moving to single table inheritance we had a few classes that looked something like this:

class Organization < ActiveRecord::Base
end
 
class Client < ActiveRecord::Base
  belongs_to :organization
end
 
class StaffMember < ActiveRecord::Base
  belongs_to :organization
  has_many :clients, :through => :organization
end

After moving to single table inheritance it was something like this:

class Organization < ActiveRecord::Base
end
 
class Client < Organization
  belongs_to :organization, :foreign_key => 'parent_id'
end
 
class StaffMember < ActiveRecord::Base
  belongs_to :organization
  has_many :clients, :through => :organization
end

It looked fine. I had only changed one thing in the client model, but now the has_many association in the StaffMember model just doesn't work anymore. I kept getting an error akin to:

ActiveRecord::StatementInvalid: Mysql::Error: #42000Not unique table/alias: 'organization': SELECT organization.* FROM organization organization.parent_id = organization.id WHERE ((organization.organization_id = 1)) AND ( (`organization`.`type` = 'Client' ) )

Well that just plain sucked... all of that refactoring seemed to have gone to waste. I scoured the net looking for a solution to my conundrum and I couldn’t find a single reasonable answer to it. I could have used the "finder_sql" option but then it only returns read only pseudo models. There just had to be a better way. I begrudgingly started to dig through ActiveRecord hoping to find a quick fix. Alas, I came back empty handed. I had almost lost hope when I realized I could just like the two foreign keys together between the Client and StaffMember model to get the same result as using the Rails has_many :through option. So I dug around a little bit more in ActiveRecord and added an “alternate_key” option to the HasManyAssociation class to define a different foreign_key instead of always needing to link to a primary_key. So now I could link directly to the Client model from the StaffMember model without needing to use the :through option. An example can be seen below:

class Organization < ActiveRecord::Base
end
 
class Client < Organization
  belongs_to :organization
end
 
class StaffMember < ActiveRecord::Base
  belongs_to :organization
  has_many :clients, :alternate_key => 'organization_id', :foreign_key => 'organization_id'
end

Now @staff_member.clients should return a list of clients that are part of that StaffMember’s organization.

Download it

I have included the source below. Simply unzip it and drop it into your project’s lib directory and you should be all set. Download

Questions, Comments, Contiributions? Contact Me.

It’s really pretty simple at the moment and it only supports standard has_many relationships. If anyone has any questions or is interested in contributing ideas or code, please feel free to contact me at: A special thanks to Philip Koebbe for pointing out some flaws in my original post.

[Slashdot] [Digg] [Reddit] [del.icio.us] [Facebook] [Technorati] [Google] [StumbleUpon]
 
 

A piece of your mind...

  1. Web 2.0 Announcer Says:

    Foreign Key to Foreign Key Relationships in Ruby on Rails

    [...]This is a downloadable ruby on rails plugin that will allows you to easily handle foreign key to foreign key relationships. This plugin extends active record and works inside your models.[...]

  2. Joshua Warchol Says:

    Nice work! I’ve made some changes to the plugin for my needs and was wondering if you could set it up on Github for collaboration. Also, does this proposed patch do the same thing?

    http://rails.lighthouseapp.com/projects/8994/tickets/292-add-has_many-primary_key-option

    I’ve also noticed that alternate_key isn’t working for eager loading.

  3. let up Says:

    I have 3 tables in MS Access, and one of the table contain the other two tables’s primary key to build the relationship. Is there any way to let MS Access to insert the forign after I insert to the data to the other 2 main tables? This is my assignment. Please advice.

Leave a Reply