phplaraveleloquentpivot

Laravel - How to query a relationship on a pivot table


My Setup

I have a many-to-many relationship setup and working with my database. This is using a pivot table that has an extra column named "linked_by". The idea of this is that I can track the user that created the link between the other 2 tables.

Below is an attempted visual representation:

permissions -> permissions_roles -> roles

permissions_roles -> persons

Visual_Representation

The Issue

The permissions_roles table has an addition column names "linked_by" and I can use the ->pivot method to get the value of this column. The issue is that it only returns the exact column value. I have defined a foreign key constraint for this linked to persons.id but I can't manage to work out a way to query this from a laravel Eloquent model.

The Question

How do I query the name of the person linked to the "linked_by" column form the Eloquent query?

Ideally, I would like the query to be something like:

permissions::find(1)->roles->first()->pivot->linked_by->name;

BUT, as I haven't defined an eloquent relationship for this pivot table column I can't do this but I can't work out how I would do this if it is even possible?

Is the only way to do this to do:

$linkee = permissions::find(1)->roles->first()->pivot->linked_by;
$person = person::find($linkee);
$person->name;

Solution

  • ->using();

    I have discovered that Laravel has a way to do what I wanted out of the box by creating a model for the pivot table.

    This works by adding ->using() to the return $this->belongsToMany() model function.

    By putting the name of the newly created pivot model inside the ->using() method, we can then call any of the functions inside this pivot model just like any other eloquent call.

    So assuming that my permissions belongs to many roles and the pivot table has a 3rd column named "linked_by" (which is a foreign key of a user in the Users table):

    My permissions model would have:

    public function roles()
        {
          return $this->belongsToMany('App\roles','permissions_roles','permissions_id','roles_id')
            ->using('App\link')
            ->withPivot('linked_by');
        }
    

    and the new link model would contain:

    Notice the extends pivot and NOT model

    use Illuminate\Database\Eloquent\Relations\Pivot;
    
    class link extends Pivot
    {
        //
        protected $table = 'permissions_roles';
    
        public function linkedBy()
        {
            return $this->belongsTo('App\users', 'linked_by');
        }
    }
    

    Obviously you would need to define the opposite side of the belongsToMany relationship in the roles model, but once this is done I can use the following to pull the name of the person that is linked to the first role in the linked_by column:

    permissions::find(1)->roles->first()->pivot->linked_by->name;