The perfect database schema is one where you don’t need to concede form to any obscure requirements. In that perfect scenario all associated models could be individually identified with a single foreign key. According to the Rails guides, you can only define an association’s foreign key with a single column.
We live in an imperfect world with many obscure business requirements, so sometimes we need to use two keys. One common scenario is when you need to centralize and synchronize data from multiple outside API’s. Luckily, there is a solution.
This example will be based on an Entity Attribute Value (EAV) mapping scenario.
- Entity and EntityAttribute models, which are joined with an EntityAttributeValue model.
- EntityAttribute represent different data types, so there is a EntityAttributeValue#value method which casts the stored value to the correct data type.
- In specific cases, the actual value must be selected from a list of EntityAttributeOption.
In order to identify all Entity records that are associated with EntityAttributeOption, we need to select the EntityAttributeValue records that have the same EntityAttribute and value as the EntityAttributeOption.
class EntityAttributeValue < ActiveRecord::Base
belongs_to :entity
belongs_to :entity_attribute
belongs_to :entity_attribute_option,
->(join_or_model) {
if join_or_model.is_a? EntityAttributeValue
where(entity_attribute_id: join_or_model.entity_attribute_id)
else
where('entity_attribute_options.entity_attribute_id = entity_attribute_values.entity_attribute_id')
end
},
foreign_key: 'value',
primary_key: 'value'
end
class EntityAttributeOption < ActiveRecord::Base
belongs_to :entity_attribute
has_many :entity_attribute_values,
Proc.new { |join_or_model|
if join_or_model.is_a? EntityAttributeOption
where(entity_attribute_id: join_or_model.entity_attribute_id)
else
where('entity_attribute_values.entity_attribute_id = entity_attribute_options.entity_attribute_id')
end
},
foreign_key: 'value',
primary_key: 'value'
end
To accomplish an association with multiple foreign keys, a dynamic scope is used to determine the direction of the association and apply different queries to identify associated records using shared the EntityAttribute and the dynamic value.
