Laravel - Additional relationship on a pivot table

Question

I have a regular pivot table with 2 keys. However, I also have a 3rd column where I want to store a different key with a one to many relationship. Is this possible to have?

Example:

Pivot table:
Organization 1 | Organization 2 | Relation type
1 | 2 | 1
1 | 3 | 2

In this case organization number 1 has a relation with organization number 2 with the relation type being number 1. Organization number 1 also has a relation with organization number 3 with relation type 2.

Now is my question, how do I set up that additional one to many relationship on the pivot table?

Answer 1

What you have here is a ternary relationship. You are saying that an organisation A relates with an organisation B and a relationship type. This is a very uncommon use case because in the vast majority of cases ternary relationships can be simplified to binary ones. You need a very deep inspection of your data model to determine whether your case can be simplified, but assuming that it can't here's my suggestions.

It's worth checking the eloquent docs in particular under Defining Custom Intermediate Table Models for this. Note that this requires Laravel 5.4+ to work.

The following should work:

class OrganisationOrganisationLink extends Pivot {
    public relationType() {
          return $this->belongsTo(RelationType::class); //You need to specify the foreign key correctly as a 2nd parameter
    }
}

Then in your original model:

class Organisation extends Model {
    public relatedOrganisation() {
        return $this->belongsToMany(self::class)->using(OrganisationOrganisationLink::class); 
    }
}

Then when making practical use of this you can e.g. do:

$organisation = Organisation::with('relatedOrganisation')->first();
echo "Got ".$organisation->name." which relates to "
     .$organisation->relatedOrganisation->first()->name 
     ." with relationship type "       
     $organisation->relatedOrganisation->first()->pivot->relationshipType()->value('name'); 

Of course the fields I've assumed may not exist but hopefully you get the idea.