DataMapper saving multi-field join table

[eluser]msteudel[/eluser]
I have the following tables:

Code:

Scenarios
id | scenario_name | etc.

Locations
id | location_name | etc.

Sites
id | site_name | etc.

Types
id | type_name | etc.

scenarios_sites
id | scenario_id | location_id | site_id | type_Id | created_on | updated_on

I'm trying to figure out how to save this sort of relationship ...

I tried something like this:

Code:

$site = new Site();
        $site->where( 'id', 3)->get();

        $loc = new Location();
        $loc->where( 'id', 4 )->get();

        $type = new Type();
        $type->where( 'id', 2 )->get();

        $scen = new Scenario();
        $scen->scenario_name = 'Test';
        $scen->save( array( $site, $loc, $type ) );

I get this error:

Code:

Unable to relate scenario with location.

Can anyone point me int he right direction.

TIA

[eluser]msteudel[/eluser]
I also tried something like:

Code:

$var = array(
            'site_id' => 3,
                'location_id' => 4,
                'type_id' => 2,
                'created_on' => date( "Y-m-d H:i:s" ),
                'updated_on' => date( "Y-m-d H:i:s" ) );

$scen->save( $var );

But got this error:

Unable to relate scenario with site_id.

[eluser]msteudel[/eluser]
I realized that I'm probably suposed to do it more like this:

Code:

// no id in name and pass in objects probably
$var = array(
            'site' => $site,
            'location' => $loc,
            'type' => $type,
            'created_on' => date( "Y-m-d H:i:s" ),
            'updated_on' => date( "Y-m-d H:i:s" ) );

But I get

Unable to relate scenario with location.

[eluser]WanWizard[/eluser]
Datamapper doesn't support a through-table or relationship table with more than two foreign keys.

You will have to split the many-to-many relations, by creating a model for the through table, and create one-to-many relations to this table. It will make maintaining the relations a bit more complex though.

[eluser]msteudel[/eluser]
Thanks for the reply WanW, I'll give that a try and see how it goes ... maybe i'll just use sql for maintaining this ....