Laravel belongsToMany pivot with multiple columns

Question

I currently have two tables in the DB and a pivot table to join them when I need to do a belongsToMany lookup. The basic example is one DB table is 'teams' and the other is 'members'. I can utilize the belongsToMany method on both the team and members model to pull their relationship with each other. A team can have many members, and a member can belong to many teams.

public function teams()
{
  return $this->belongsToMany(Team::class);
}

public function members()
{
  return $this->belongsToMany(Member::class);
}

Pivot: team_member
team_id  |  member_id
---------------------
   1     |      1
   2     |      1
   3     |      2
   1     |      2

How can I expand on that pivot table to include a type of member for each team? For example, member1 is a leader on team1. member1 is an assistant on team2. member1 is a generic member on team3... and so on. Can I just add a column to that same pivot table? Would it be the membertype_id? How can I relate that to another model/table?

Possible duplicate of [getting the value of an extra pivot table column laravel](https://stackoverflow.com/questions/26566675/getting-the-value-of-an-extra-pivot-table-column-laravel) — adprocas, Mar 19 '18 at 19:32

Tim Lewis · Accepted Answer · 2018-03-19T19:49:02.590

17

This is pretty common, and Laravel handles it already. Add extra columns to the pivot table and expand your relationships to use withPivot():

public function teams(){
  return $this->belongsToMany(Team::class)->withPivot(["teamRole", ...]);
}

Then accessing is as simple as:

$user = \App\User::with(["teams"])->first();
$team = $user->teams->first();
$teamRole = $team->pivot->teamRole;

See the Documentation for more information:

https://laravel.com/docs/5.6/eloquent-relationships

To answer the second part, which is essentially a "Triple Pivot", that requires extra functionality. See

Laravel - Pivot table for three models - how to insert related models?

for more information, and an associated Package (by the answerer on that question, not maintained, but good for an example)

https://github.com/jarektkaczyk/Eloquent-triple-pivot

edited Mar 19 '18 at 19:49

answered Mar 19 '18 at 19:35

Tim Lewis

27,813
13
73
102

I was heading in that direction, but couldn't grasp how to relate to another column in the pivot table. Looks like I can do a "team_member_role" pivot table each column having a model. Then I can do something like: $team->members->find($id)->roles()->wherePivot('member_id', $member->id)->get(); – hiddenicon Mar 19 '18 at 20:24
Nice; at a glance that looks like it should work. I've only ever come across non-relation columns on pivots (`price`, for example), but it looks like it's possible to handle another layer of complexity. Cheers! – Tim Lewis Mar 19 '18 at 20:27
Circling back on this. How can I get one collection with the 3 relations so I can list it out? Almost like a roster sheet. For one team, list all members and their role for that team. And to do that with one Eloquent call??? – hiddenicon Apr 03 '18 at 02:51
If you've got another question, it's best to ask another one and reference this if necessary. At a high-level, you'd just use multiple values in the `->with()` clause, such as `->with(["users", "users.role", ...])` etc to get all info in a single eloquent call. – Tim Lewis Apr 03 '18 at 13:59

Laravel belongsToMany pivot with multiple columns

1 Answers1

Linked