Table with two foreign keys to the same lookup table

[SM_]

Hi,

I am new to Ruby, I am trying to use Rails to build a web interface for a legacy engineering database. Problem what I am facing now is that in many cases one table has 2 references to the same lookup table, for example table ValveCatalog has 2 fields: NPSMinKey and NPSMaxKey - references to the same table NPS with primary key NPS_ID. Or another example - each valve has 2 fields for material - body material and bolt material.

The question is - how should I describe this situations in the model, I don't think I can use belongs_to ... any ideas?

Thanks,

Sergey

[ReggieB]

You can mimic the belongs_to behaviour to do what you want. All that belongs_to does is create a method that returns an object that match the criteria. The clever bit is the way it uses the Rails conventions to work out what needs to be returned with the minimum of effort.

So if you have a Child class that contains:

belongs_to :parent

The system will work out from normal Rails conventions that there will be two tables (children and parents) and that the children table will have a parent_id field. It will then be able to automatically generate the SQL code that will return the Parent object that is associated to a particular Child via its parent_id via something like:

SELECT * FROM parents WHERE id = 3

where 3 in the entry in parent_id for the current child.

You could replicate this behaviour with a method like this in the Child model:

def parent
  Parent.find(:first, :conditions => ["id = ?", parent_id])
end

Therefore, for your instance, what you need to do is mimic the parent method created via

belongs_to :parent

with you're own custom parent method that alters the Parent.find criteria to suit your custom database.

Perhaps something along the lines of:

def valve
  Valve.find(:first, :conditions => ["NPSMinKey = ? AND NPSMaxKey =?", something, something_else])
end

You may also want to mimic "has_many :children" by creating a children method in your Parent class with something like:

def children
  Child.find(:all, :conditions => ["parent_id = ?, id])
end

Which will return an array of Child objects whose parent_id match the current Parent's id.