How to connect 3 table with a pivot table in Laravel?

Question

(First of all sorry for my english)

Maybe the three way pivot table is not the best solution for my problem, but I don't know. So I have a Project table and a Member table. One project can have a lot of members, and these members can work on other projects. So I created a member_project table, where I store the project_id and the member_id.

I need to upgrade my database, so if I connect a project with a member I need to set the member's position in that project (owner, participant, etc.). So my first idea was that I have another table and model: Position, with an ID and the name of the position and I have to upgrade my member_project table with position_id field. But how? And is it the best solution? Because with this I can add the same project_id and member_id with more than one position and I don't want this. I want to connect a project with a member with one position. The project can have more than one member with different positions and a member can work for one project as the owner of this project, but can work for an antoher project as just a worker.

Answer 1

To define this relationship, three database tables are needed: projects, members, and project_member.

For member's position you can add an extra column position in the pivot table (project_member)

You have to define members method on Project model:

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class User extends Model
{
    /**
     * The members that belong to the project.
     */
    public function members()
    {
        return $this->belongsToMany('App\Member')->withPivot('position');
    }
}

And in Member model, you have to define the Inverse of the relationship:

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Member extends Model
{
    /**
     * The projects that belong to the member.
     */
    public function projects()
    {
        return $this->belongsToMany('App\Project')->withPivot('position');
    }
}

After the relationship is defined Eloquent method will help to retrieve collection.

For more information about many to many relationships, here is link

When attaching a relationship to a model, you can pass an array of additional data(in your case position) to be inserted into the intermediate table:

$project->members()->attach($memberId, ['position' => "worker"]);

In place of worker you can pass variable $position with any value.

And for other many to many techniques, you will get information from here many to many relationships