How to define three way many to many relationshop in Laravel?

Question

I'm having a hard time creating a three way many to many relationship between three tables/models in Laravel 5. And so far looking up from the web hasn't given me any workable solution.

I have three models: User, Role, Thesis.

What I would like to achieve is to connect the three with each other. For example a user can be associated with many theses and this user can have many different roles on one specific thesis.

So a user can have many roles (instructor and reviewer for example) on one specific thesis and the same user can also be associated with another thesis and having another role on that as well (for example instructor only).

So far I've tried an approach which means creating another model that would handle the pivot table. So I created a model Relation.

Tables I have at the moment:

users

id 
name

theses

id 
topic

roles

id 
name

relations

id
role_id (references id on roles) 
thesis_id (references id on theses)
user_id (references id on users)

And the code I have right now

User.php

class User extends Authenticatable
{
    public function relations(){
        return $this->hasMany('App\Relation', 'relations');
    }
}

Thesis.php

class Thesis extends Model
{
    public function relations(){
        return $this->hasMany('App\Relation', 'relations');
    }
}

Role.php

class Role extends Model
{
    public function relations(){
        return $this->hasMany('App\Relation', 'relations');
    }
}

Relation.php

class Relation extends Model
{
    public function user(){
    return $this->belongsToMany('App\User');
    }

    public function thesis(){
        return $this->belongsToMany('App\Thesis');
    }

    public function role(){
        return $this->belongsToMany('App\Role');
    }
}

Right now I'm using $relation = new Relation; approach when inserting into the pivot table.

I don't know how I can get the data from the pivot table. For example get thesis of user and what kind of roles the user has on that specific thesis.

I've tried something like $user->thesis() or $user->role() or $user->thesis()->role() but none of them don't work. I'm only getting an undefined method error.

I would really appreciate if I could get some help or at least some kind of direction if even what I've showed here is somewhat correct.

Thank you.

Answer 1

You're interested in a Many to Many relationship which stores the relationship through an intermediate table. An intermediate table can have additional attributes stored on it, therefore, you can create a thesis_users intermediate table with an additional role_id attribute. The documentation covers this use case under the heading "Retrieving Intermediate Table Columns". You do not need an additional model to represent these relationships.

You can query the intermediate table (also referred to as a pivot table) if you need to determine which Thesis a user has specific roles on, e.g:

return $this->belongsToMany('App\Thesis')->wherePivot('role_id', 1);

Your User model relationship would look something like this:

public function theses()
{
    return $this->belongsToMany('App\Thesis');
}

Your Thesis model relationship would look something like this:

public function users()
{
    return $this->belongsToMany('App\User');
}

Then you can do additional relationship constraints on your Thesis model, like this:

public function authors()
{
    return $this->belongsToMany('App\User')->wherePivot('role_id', 1);
}


public function reviewers()
{
    return $this->belongsToMany('App\User')->wherePivot('role_id', 2);
}


public function instructors()
{
    return $this->belongsToMany('App\User')->wherePivot('role_id', 3);
}

And on your User model like this:

public function authored()
{
    return $this->belongsToMany('App\Thesis')->wherePivot('role_id', 1);
}

public function reviewer()
{
    return $this->belongsToMany('App\Thesis')->wherePivot('role_id', 2);
}

etc.

Thesis::find(1)->reviewers(); // returns every App\User with the role_id 2 on App\Thesis with ID 1
Thesis::find(1)->instructors(); // returns every App\User with the role_id 3 on App\Thesis with ID 1

User::find(1)->theses(); // returns each App\Thesis that the user has any role on 
User::find(1)->authored(); // returns each App\Thesis that the user has the author role on