3-way Relationships with Laravel and Eloquent

Question

I've already posted this on the laravel forums but nobody was able to offer any help. Thought I'd post here for a second opinion.

I need a little advice/help with how to structure my models. Here's what I'm trying to do

A User can be a member of multiple Teams, but the user can fulfill a different Role on each team.

The idea being that the user sees a different set of data/features depending on the role they occupy on the team.

So I had thought about something like:

Users
id, name, email, etc...

Teams
id, name, description

Roles
id, name

Team Users
user_id, team_id, role_id

The team_members table would tie the 3 pieces together. Defining what teams a user belongs to and the role they have.

My questions are:

1. Is this the best way to go about such a relationship
2. How would I assign a user to a team with a role?
3. Using Eloquent, how would I get a users role on a given team?

Answer 1

From your scenario, the tables that you define looks fine, to assign a user to a team and his role, provided you create a model TeamMember for the table you can do something like:

//assign or update user team or role
$new_member = TeamMember::firstorCreate(array(
              'user_id' => $this->user->id,
              'team_id' => $team_id,
              'role_id' => $role_id,
              ));

//accessing the role of a user on a team
$role = TeamMember::where('user_id', '=', $user_id)
        ->where('team_id', '=', $team_id)->first()->role_id;

//get the name of the role
$role_name = Role::find($role)->name;