Rails Associations With Multiple Foreign Keys

May 31, 2013 Joseph Palermo

Recently we had a situation where we inherited a schema and two of the models were joined using multiple foreign keys. The Rails associations API doesn’t appear to offer any good solutions to this problem. You can specify a single foreign_key and a single primary_key, but nothing really for multiple keys. One solution would be to use the Proc syntax for the :conditions option to specify the second column.

has_many :others,
  :foreign_key => :fk_1,
  :primary_key => :first_primary_column,
  :conditions => Proc.new {
    {:fk_2 => other_primary_column}
  }

Here the proc is called on our instance of the primary model, so we can just reference the other_primary_column and the value of the current instance will be used in the conditions.

This will work for single models, but will not work if you are trying to eager load the association with an `includes` statement. For eager loading, what we really want to do is join the association table which is how eager loading was done prior to Rails 2.1.

Rails will fall back to joining an included association, but only if it detects that you are trying to reference it in your conditions for the primary model that you are finding. Since we are referencing it in the conditions of the association instead, we’ll have to tell it to join ourselves whenever we do the eager load.

Model.joins(:others).includes(:others)

Now we need to specifying the join conditions for the association, we can do this right in the conditions:

has_many :others,
  :foreign_key => :fk_1,
  :primary_key => :first_primary_column,
  :conditions => 'others.fk_2 = other_primary_column'

The problem here is that the association now ONLY works if you join and eager load it. If you have a single instance where the association was not eager loaded, the association won’t work.

We can combine the two solutions by relying on the fact that when you do an eager load, the conditions proc gets passed the JoinAssociation. We don’t really need the JoinAssociation, but we can use it to switch between the two cases.

has_many :others,
  :foreign_key => :fk_1,
  :primary_key => :first_primary_column,
  :conditions => Proc.new { |join_association|
    if join_association
      'others.fk_2 = other_primary_column'
    else
      {:fk_2 => other_primary_column}
    end
  }

Now our association will behave correctly if we load it from a single instance, or if we try to eager load it with an `includes` statement (as long as we remember to also `joins` the association too).

Keep in mind that eager loading by doing a join was changed from the default in Rails 2.1 for a good reason. If you include a few associations and one or more of them is a has_many, you end up returning a lot of extra data that is not used. Doing a single query per table is more efficient in general, but with multiple foreign keys you have to do a join to eager load them.

About the Author

Biography

More Content by Joseph Palermo
Previous
Studio time and the road to pair designing
Studio time and the road to pair designing

We take collaboration seriously around here, as you may be aware. For developers, it’s pair up or go home. ...

Next
Apps for Health: The mHealth Renaissance
Apps for Health: The mHealth Renaissance

We recently had the opportunity to attend Mohawk College’s Apps for Health conference and further explore t...

Enter curious. Exit smarter.

Register Now