Laravel 5 eloquent hasManyThrough / belongsToManyThrough relationships

Question

In a Laravel 5.2 application I have three models: User, Role and Task. A User is associated with multiple Roles, and a Role is associated with multiple Tasks. Therefore each user is associated to multiple tasks, through their roles.

I am trying to access all Tasks associated with a User, through their Roles.

The relevant parts of my models look like:

class User extends Authenticatable
{    
    public function roles()
    {
        return $this->belongsToMany('App\Role');
    }

    public function tasks()
    {
        return $this->hasManyThrough('App\Task', 'App\Role');
    }
}

class Role extends Model
{
    public function tasks()
    {
        return $this->belongsToMany('App\Task');
    }

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

class Task extends Model
{    
    public function roles()
    {
        return $this->belongsToMany('App\Role');
    } 
}

The following returns an SQL error;

Column not found: 1054 Unknown column 'roles.user_id'

It seems to be trying to access the relationship through a (non-existent) foreign key in the Role model, rather than through the pivot table.

$user = Auth::user;
$tasks = $user->tasks;

How can I access all tasks related to a user through these relationships?

what foreign key column do you have in role table for saving user id? — Alankar More, Feb 10 '16 at 13:04

score 4 · Answer 1 · edited May 23 '17 at 12:26

I have developed a custom BelongsToManyThrough relationship which might interest you. You would need to add the new relation class (as given in my gist; it is too long to paste here), and also override your base Model class as described in the gist to implement belongsToManyThrough.

Then (assuming you are using Laravel's default table naming schemes - if not, you can specify the joining tables as well), you would define your relationship as:

public function tasks()
{
    return $this->belongsToManyThrough(
        'App\Task',
        'App\Role');
}

belongsToManyThrough will not only give you a list of Tasks for your User(s), it will also tell you the Role(s) via which each User has each Task. For example, if you had:

$user->tasks()->get()

The output would look something like:

 [
    {
        "id": 2,
        "name": "Ban spammers",
        "roles_via": [
            {
                "id": 2,
                "slug": "site-admin",
                "name": "Site Administrator",
                "description": "This role is meant for \"site administrators\", who can basically do anything except create, edit, or delete other administrators."
            },
            {
                "id": 3,
                "slug": "group-admin",
                "name": "Group Administrator",
                "description": "This role is meant for \"group administrators\", who can basically do anything with users in their same group, except other administrators of that group."
            }
        ]
    },
    {
        "id": 13,
        "name": "Approve posts",
        "roles_via": [
            {
                "id": 3,
                "slug": "group-admin",
                "name": "Group Administrator",
                "description": "This role is meant for \"group administrators\", who can basically do anything with users in their same group, except other administrators of that group."
            }
        ]
    },
    {
        "id": 16,
        "name": "Reboot server",
        "roles_via": [
            {
                "id": 2,
                "slug": "site-admin",
                "name": "Site Administrator",
                "description": "This role is meant for \"site administrators\", who can basically do anything except create, edit, or delete other administrators."
            }
        ]
    }
]

My custom relationship does this efficiently, with only a few queries, as opposed to other solutions involving foreach, which would create an n+1 query problem.

hello alexw where to put this custom relationship query? i am not clear — sabin maharjan, Sep 22 '17 at 09:35
@user2851129 there is actually an updated version of this integrated into our UserFrosting framework: https://github.com/userfrosting/UserFrosting/tree/master/app/sprinkles/core/src/Database/Relations. See the `Unique` trait, `BelongsToManyThrough` relationship class, and our custom [`HasRelationships`](https://github.com/userfrosting/UserFrosting/blob/master/app/sprinkles/core/src/Database/Models/Concerns/HasRelationships.php) Model trait. If you take a look at the Laravel API docs, you can get a feel for how to integrate these. You can also join us in [chat](https://chat.userfrosting.com) — alexw, Sep 22 '17 at 15:29

score 0 · Answer 2 · answered Feb 10 '16 at 13:34

From the source code you shared it seems that you have Many to Many relationships between: User and Role and between Role and Task.

The hasManyThrough method expects two One to Many relationships.

A possible way of getting all User related Tasks would be: (inside the User class)

public function getTasksAttribute()
{
    $tasks = [];
    foreach ($this->roles as $role) {
        $tasks = array_merge($tasks, $role->tasks);
    }
    return $tasks;
 }

You would then be able to access the tasks with:

$user->tasks;

upsidedownb · Answer 3 · 2020-08-09T06:47:04.057

0

Instead of using array_merge()

One way to do this would be to add a getTasksAttribute() method, and using collections.

public function getTasksAttribute()
{
    $tasks = new Collection([]);
    foreach ($this->roles as $role) {
        $tasks = $tasks->mergeRecursive($role->tasks);
    }
    return $tasks;
}

This can be accessed with $user->tasks

edited Aug 09 '20 at 06:47

answered Aug 09 '20 at 05:11

upsidedownb

39
1
2
5

score 0 · Answer 4 · edited Dec 24 '20 at 13:46

If you have 3 models

User, Role, Permission

User belongsToMany Role

Role belongsToMany User

Permission belongsToMany Role

Role belongsToMany Permission

to check if a user has specific permission I made this function in User model

 public function hasPermission($permission)
    {
        return Permission::where('name', $permission)->whereHas('roles', function ($query) {
            $query->whereHas('users', function ($query) {
                $query->where('users.id', $this->id);
            });
        })->exists();
    }

Laravel 5 eloquent hasManyThrough / belongsToManyThrough relationships

4 Answers4

Linked