[Kansas-general] many-to-many relationships?

[Kirk Haines]

On Fri, 13 Aug 2004 18:08:10 -0400, Nathan Weston wrote
> Does kansas have any support for many-to-many relationships?
> 
> In particular, I'm thinking of the pattern where you have two tables,
>  A and B, and you use a third table, C, to model the many-to-many 
> relationship between them.
> 
> It seems this would be easy to handle in kansas, in the same spirit 
> as to_one and to_many. Is it supported?
> 

Hmmm.  Not directly, yet.

Let's say you have a table, Laboratories, and a table, Chemicals.

You're tracking what chemicals are used in what labs.  So, you have multiple 
labs and multiple chemicals.

You'd have to have a third table -- we'll call it labXchem -- to cross 
reference the laboratoris and the chemicals.

Right now you'd have an intervening step:

KSDatabase.Laboratories.to_many(:chemicals, :LabXChem, :lab_idx)
KSDatabase.LabXChem.to_one(:chemical, :chem_idx, :Chemical)
KSDatabase.Chemicals.to_many(:labs, :LabXChem, :chem_idx)
KSDatabase.LabXChem.to_one(:lab, :lab_idx, :Laboratories)

Thus:

@labs = ksdbh.select(:Laboratories)
lab = @labs.first
@chemicals = lab.chemicals
chem = @chemicals.first
real_chemical_data = chem.chemical

It works, but I am the first to agree that this is not idea.

To do a many to many, we need to know the intervening table, the destination 
table, and the fields in the intervening table that map to the primary keys 
in each table.

What do you think of a syntax like this:

many_to_many(relationship_name, intervening_table, pk_of_source_table, 
pk_of_destination_table, destination_table)

i.e.

KSDatabase.Laboratories.many_to_many
(:chemicals, :LabXChem, :lab_idx, :chem_idx, :Chemical)


Thus with @labs as in the above bit of code, @labs[0].chemicals will return 
an array of chemical records for all of the chemicals in the lab.


So, what do you think?


Kirk Haines