Using 3-way join tables in Yii 2 Active Record

3-way-joins.md

I have an interesting Yii 2 AR design problem.

The DB has 12 primary entity tables related via a full mesh of 72 join tables. Each relation also has a type (and attributes) but those aren't stored in the join tables – they are in additional tables that the 72 join tables reference. So each of the 72 join tables provides a 3-way join between one entity, another entity and another table called link that points to the link_type.

My problem is to write the AR models and relation methods.

Let's make this more concrete with two of the primary entities: artist and recording, related through l_artist_recording. Each record of l_artist_recording has an FK to the link table, through which I can load the link_type name. For example, an artist-recording relation might have link_type "performer", "conductor", or "producer" etc.

This is a simplified schma good enough for thinking about the AR problem.

An obvious approach is to write an AR model for each of the tables in the diagram. Then Artist models can relate to Recordings and LinkTypes via ArtistRecording. This allows Artist to know the LinkType of the relation to individual Recording model because Recording and LinkType are both properties of each ArtistRecording model. But this requires 72 models for the 72 join tables and it's clumsy to work this way.

I would far prefer if link_type.name were populated into related Recording models when they are loaded. Then, for example, if $artist is an Artist model instance it would have $artist->recordings which is an array of Recording models, each of which has a Recording::$name column attribute loaded from recording.name and a Recording::$link_type_name instance variable loaded from link_type.name.

I can relate Artist to Recording without needing a model for artist_recording

    public function getRecordings() {
        return $this
            ->hasMany(Recording::className(), ['id' => 'recording_id'])
            ->viaTable('artist_recording', ['artist_id' => 'id']);
    }

But I don't know how to elaborate that so it loads fields from tables related via the third leg of the 3-way join into the Recording models. I am inclined to believe AR can do this. But I don't know how.

Easy - add this to your Recording model.

`
public function getArtist()
{
return $this->hasOne(Artist::class, ['id' => 'artist_id'])->via('artistrecording');
}

public function getArtistrecording()
{
    return $this->hasOne(Artistrecording::class, ['recording_id' => 'id']);
}

`