Close
Glad You're Ready. Let's Get Started!

Let us know how we can contact you.

Thank you!

We'll respond shortly.

LABS
Rails Associations With Multiple Foreign Keys

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.

Comments
  1. Cathy says:

    This seems like a good solution for associations. If you need to have multiple primary keys on a model itself, you can set them using the following gem (recently updated).
    http://github.com/drnic/composite_primary_keys

  2. Joseph Palermo says:

    Unfortunately there is a pretty big problem with what I wrote above. The joins statement will perform an inner join, which is not normally what you want to do when eager loading.

    However, if you try to specify a left join yourself by using AREL or just a plain string, the logic in ActiveRecord will not see that you are joining the eager loaded tables and will not try to eager load them any more.

    The only solution I could find was to trick ActiveRecord into thinking that you are referencing the eager loaded tables in your conditions, which causes it to fall back on the left join eager loading method. The regex it uses for detecting tables in a query is pretty simplistic and conservative (for instance, if you have a schema name before your table it will detect that as a separate table and fall back on the join syntax.

    If you add a nonsense condition that looks like a table you can force it to fall back on the join syntax:
    Model.includes(:others).where(“‘aa.’ = ‘aa.'”)

    That’s a pretty ugly hack, but currently ActiveRecord doesn’t seem to offer any other options. If you do need to go this route, I’d suggest hiding the where and the includes together in a class method with a good name.

  3. Tom Wilson says:

    Nice. Almost everything we do has to interface with legacy data and we’re stuck with multi-field primary keys.

    We’ve been solving this problem similarly but instead of:

    :conditions => Proc.new { |join_association|
    if join_association
    ‘others.fk_2 = other_primary_column’
    else
    {:fk_2 => other_primary_column}
    end

    we’ve used this form: (reformatted to show similarity.)

    :conditions => Proc.new { # no arg list
    unless self.respond_to?(:other_primary_column)
    ‘others.fk_2 = other_primary_column’
    else
    {:fk_2 => self.other_primary_column}
    end
    }

    (http://stackoverflow.com/a/15536920/1843864)

    The idea’s similar, but I like your solution because:
    1) the intention of your test (“if join_association”) is clearer and
    2) it avoids the overhead of calling respond_to? which always seems like a hack to me.

  4. Felix Kenton says:

    I found it necessary to change the syntax of the hash form to get a “has_many :through =>” to work properly.

    Using the “table_name.field” hash key format instead of :field takes away any ambiguity for ActiveRecord.

    :conditions => Proc.new { |join_association|
    if join_association
    ‘others.fk_2 = other_primary_column’
    else
    {‘others.fk_2′ => other_primary_column}
    end
    }

  5. Peck says:

    Great post, however doesn’t work with Rails4 as conditions have been deprecated and scopes aren’t dynamic

    Any insight on how to do the same thing in Rails4?

  6. Joseph Palermo says:

    In Rails 4 you can replace conditions with a lambda passed to the association declaration. Rails 4 seems to have changed somewhat though. The lambda is no longer in the scope of the model when you have a single one, so you can’t just call “other_primary_column” to get the value, instead the model is actually passed to the lambda. This means we have to do a check to see if the value we are passed is the model, or the JoinAssociation.

    I haven’t used this before, so there may be some edge cases that this doesn’t work with, but a quick test shows it works for the two base cases.

    has_many :others, -> (join_or_model) {
        if join_or_model.is_a? JoinDependency::JoinAssociation
          where(fk_2: Model.arel_table[:pk_2])
        else
          where(fk_2: join_or_model.other_primary_column)
        end
      },
      :foreign_key => :fk_1,
      :primary_key => :first_primary_column
    
  7. Sascha says:

    Fighting for hours with this problem. With this piece of code everything works with Rails 4. Even longer find_or_initialize_by chains!

    Thank you so much for this comment!

  8. Kevin says:

    Just to expand on the Rails 4 implementation. I had to use the following setup in order to use a :through realtionship. I believe I have tested it in every possible direction.

    class ProductAttributeValue (join_or_model) {
    if join_or_model.is_a? ProductAttributeValue
    where(product_attribute_id: join_or_model.product_attribute_id)
    else
    where(‘product_attribute_options.product_attribute_id = product_attribute_values.product_attribute_id’)
    end
    },
    foreign_key: ‘value’,
    primary_key: ‘value’
    end

    class ProductAttributeOption (join_or_model) {
    if join_or_model.is_a? ProductAttributeOption
    where(product_attribute_id: join_or_model.product_attribute_id)
    else
    where(‘product_attribute_values.product_attribute_id = product_attribute_options.product_attribute_id’)
    end
    },
    foreign_key: ‘value’,
    primary_key: ‘value’
    has_many :products, through: :product_attribute_values
    end

    class Product < ActiveRecord::Base
    has_many :product_attribute_values, dependent: :destroy
    has_many :product_attribute_options, through: :product_attribute_values
    end

  9. Ismail Akram says:

    @joseph Palermo your last comment is correct but it breaks when you have ‘includes’ in your statement so for that case we need to return none and this handle all cases.

    has_many :others, -> (join_or_model) {
    if join_or_model.is_a? JoinDependency::JoinAssociation
    where(fk_2: Model.arel_table[:pk_2])
    elsif join_or_model.nil?
    none
    else
    where(fk_2: join_or_model.other_primary_column)
    end
    },
    :foreign_key => :fk_1,
    :primary_key => :first_primary_column

  10. James says:

    @Ismail Akram that code won’t crash when you use includes (in two-query mode), but it will silently drop the extra foreign key, giving incorrect results. Because of this, I would recommend actually not handling that use case and letting it crash.

    The association will only work correctly when you either force the .includes into a join (one-query mode) by also calling .references(:name_of_table) or instead using .eager_load, which always uses a join.

Post a Comment

Your Information (Name required. Email address will not be displayed with comment.)

* Copy This Password *

* Type Or Paste Password Here *