Sometimes you need to add more fields to the many-to-many pivot table, and those fields may also have their own relationships.
Example: you have models User, Project, and Role.
Users may be assigned to different Projects, with a different Role in each project.
The pivot table structure would look like this:
project_user
-
project_id
(foreign key to projects) -
user_id
(foreign key to users) -
role_id
(foreign key to roles)
If that third field is a regular field, without a relationship, you define this in the Eloquent model:
app/Models/Project.php:
class Project extends Model { public function users() { return $this->belongsToMany(User::class)->withPivot('role_id'); } }
But since we have a relationship here, we can define a special Pivot model and add a relationship inside of it.
php artisan make:model ProjectUser --pivot
Then, in that Pivot model, you define the relationship to the Role.
app/Models/ProjectUser.php:
use Illuminate\Database\Eloquent\Relations\Pivot; class ProjectUser extends Pivot{ public function role() { return $this->belongsTo(Role::class, 'role_id'); }}
Then, you provide that Pivot model as a part of your many-to-many relationship, with using()
:
app/Models/Project.php:
use App\Models\ProjectUser; class Project extends Model { public function users() { return $this->belongsToMany(User::class) ->withPivot('role_id') ->using(ProjectUser::class); } }
And then, finally, you can run this query:
$projects = App\Models\Project::with('users')->get();foreach ($projects as $project) { echo $project->name. ': '; foreach ($project->users as $user) { echo $user->name . ' (' . $user->pivot->role->name . ') '; }}
As you can see, we can access the role of each user by calling $user->pivot->role->name
.
Notice: keep in mind that this code will unfortunately produce the N+1 query problem. During my testing, I haven't found a way to eager load the users.pivot.role
with this or any other syntax. If you have achieved this, please write in the comment, and I will update the article with your solution.
Hi Povilas, I would rename the many-to-many table as
engagements
.Then, I create a Model
Engagement
. This model should have 3 relationsshipsbelongsToMany
for the Project, User, and Role. I would specify theengagements
as the Pivot table.Then, I query the
Engagement
model, join on Products, Users, and Roles, and select the columns I want.That's what i would use for 3 tables having a many-to-many relationship. I am not 100% sure that's what you are looking for, but thought of sharing it.
Wow, thanks for sharing! Not sure I would do it exactly this way, but you have a good logic behind it.
Have a look at medium article and this repo to solve N+1