How would you do a multitable cross reference select box in Rails?

I've done this in other languages, but how would you handle the following issue in Rails?

Assume database tables a, b and c. Each table has id,name,text fields (and yes, there are reasons why they are separate tables - I'm simplifying here)

Any record in any table can cross-reference one or more records in that table or another table. The cross reference tables are a_a, a_b, a_c, b_b, b_c, c_c [fields are as expected a_id,b_id, etc)

So assume record 1 in table a is referenced by: record 22 in table a record 347 in table b and record 19 in table c.

When a record is displayed, I have a dropdown select box that shows the name of any cross references. The contents are generated by a database view created by multiple selects combined with union.

The obvious hurdle in a select box is returning not just the id of the selected item, but also the table in which that particular item is located. In my current (non-ruby, non-rails) implementation, this is solved by concatenating the table name with a separator and the record id

The select box thus returns the combined value which is then exploded into its parts so that the webapp can display the desired item. This isn't difficult when done manually, but what would be the appropriate way of handling the issue in the "Rails" way?

Are you free to modify the database? I'd probably try making a new model CrossReference with it's own table cross_references. This table can hold a_id, b_id and c_id, and your A, B and C each has_many :cross_references. If it makes sense for your models, A has_many :bs, :through => :cross_references etc. I don't know exactly what the details are for referencing the same table (what you have in a_a, b_b and c_c), but I imagine it's doable.

You'd have to add columns (d_id) to the cross_references table when you add a table (d) to be cross referenced. But on the other hand, in your current setup, you'd have to add an increasing number of new reference-tables a_d, b_d, c_d and d_d.

hmm.. hope that helps merry x-mas - Martin

I could modify the database to add a multitable linking table - I can't reduce the current number of linking tables because it would break existing apps that I don't control. The three real data tables have a lot of fields that are not in common and serve different purposes, so they can't really be combined.

Even if I have a single multi-table linking table, I'm not sure that solves the problem of how the select box passes the correct info.

A has_many :bs, :through => :cross_references A has_many :cs, :through => :cross_references

I'm still puzzled at what the return from the single select box will look like.


Martin Svalin wrote:

Why can't you use this method in Rails with options_for_select? You can pass in an array of 2-element arrays, each of which contains the text to display and the value to return. You can make the values just like you did before.


It almost sounds like polymorphic table inheritance? (from the agile book). I'm having a hard time visualizing what the use-case would be (ie: Employee < Person; Manager < Person; Mentor < Person subclassing?) and how that would look in the select box itself.

Think of the use case this way (again it is somewhat simplified) Table A is text of laws relevant to our company Table B is external commentary on laws (think articles) Table C is internal commentary on laws (think internal notes).

Any law can be flagged with one or more references to another law, or commentaries from either commentary table. Any commentary (external or internal) can be flagged with one or more references to one or more laws, or commentary from either commentary table.

According to our lawyers, we need to keep the external commentary separate from our internal commentary in order to avoid fights with our external suppliers.


chovy wrote:

Perhaps a commentaries table with 'type' attribute (externel or internal), and an foreign key that references the laws table and its id.

Would look something like:

create_table commentaries:    id => :integer,    type => :string, #=> (internal or external)    comment => :text    law_id => :integer #=> references end

create_table laws:   id => :integer,   name => :string,   text => :text end