HasManyThrough with two pivot tables

Published 2 years ago by wheredidgogogo

This one is starting to fry my brain...

I have five tables:

users

roles_users (pivot)

roles

permissions_roles (pivot)

permissions

I can't for the life of me create a query that returns the permissions for a given user.

I can get hasmanythrough to work with just one pivot table and a one-to-one relationship for the second step. But I can't get it to work when there are 2 many-to-many relationships.

Any help would be greatly appreciated :)

Best Answer (As Selected By wheredidgogogo)
wheredidgogogo

There was one little bit that I missed - adding the user id in the where statement - now it works :)

public function hasPermission($permission_name) 
{
    $result = \DB::table('users')
        ->join('role_user','users.id','=','role_user.user_id')
        ->join('roles','role_user.role_id','=','roles.id')
        ->join('permission_role','roles.id','=','permission_role.role_id')
        ->join('permissions','permission_role.permission_id','=','permissions.id')
        ->select('permissions.*')->where('users.id','=',\Auth::user()->id)->where('permissions.name','=',$permission_name)->count();

    return $result > 0;
}

vpavic

I think that you can't solve this situation with hasManyTrough. hasManyTrough is intended to be used when you have cascade of hasMany relations but in your case you have cascade of belongsToMany relations. In this case you can add in User model roles method like this

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

and then in Role model method like this

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

I assume that you created this roles and permissions model with pivot tables because user can have more than one role in your application. To get all permissions for user combined from all roles you must first get all roles then loop trough collection of roles and select permissions for each role that user have and somehow filter permissions to eliminate duplicates. If you want to optimise and read all permissions for user without looping then you must use query builder and manually make select with joins and using SELECT DISTINCT eliminate duplicates.

If somebody knows simpler solution please let me know.

wheredidgogogo

Hi vpavic

Thanks for your response - I was worried I wasn't going to get a solution (it was very depressing watching all the other questions getting answered!)

I thought about the looping solution, but it didn't seem efficient. I'm hoping to get the result in one line of code.

I'll have a stab at solving it with query builder.

Seems odd that laravel hasn't got a simple solution for this.

jlrdw
jlrdw
2 years ago (227,210 XP)
wheredidgogogo

Here's the solution I came up with. Seems to work, not too sure how elegant it is....

public function hasPermission($permission_name) {

    $result = \DB::table('users')
        ->join('role_user','users.id','=','role_user.user_id')
        ->join('roles','role_user.role_id','=','roles.id')
        ->join('permission_role','roles.id','=','permission_role.role_id')
        ->join('permissions','permission_role.permission_id','=','permissions.id')
        ->select('permissions.*')->where('permissions.name','=',$permission_name)->count();

    if ($result > 0) {
        return true;
    } else {
        return false;
    }

}
vpavic

I don't see that you have other option except to use DB. Only what you can do to make this more elegant is to remove that if statement on the end like this

public function hasPermission($permission_name) 
{
    return \DB::table('users')
        ->join('role_user','users.id','=','role_user.user_id')
        ->join('roles','role_user.role_id','=','roles.id')
        ->join('permission_role','roles.id','=','permission_role.role_id')
        ->join('permissions','permission_role.permission_id','=','permissions.id')
        ->select('permissions.*')->where('permissions.name','=',$permission_name)->count() > 0;
}
wheredidgogogo

There was one little bit that I missed - adding the user id in the where statement - now it works :)

public function hasPermission($permission_name) 
{
    $result = \DB::table('users')
        ->join('role_user','users.id','=','role_user.user_id')
        ->join('roles','role_user.role_id','=','roles.id')
        ->join('permission_role','roles.id','=','permission_role.role_id')
        ->join('permissions','permission_role.permission_id','=','permissions.id')
        ->select('permissions.*')->where('users.id','=',\Auth::user()->id)->where('permissions.name','=',$permission_name)->count();

    return $result > 0;
}

vpavic

Why you using Auth facade to get user id? On this way you created method which you can use to check permission only for user who's logged in. For example if you want to build admin panel in your app then you can't use this method to check if some other user have given permission because it gives you only answer do logged user have that permission.

It's logical that you put this method in User class. In that case just use $this->id instead of \Auth::user()->id to check if that user have given permission. And after that if you want to see if logged user have some permission just do

\Auth::user()->hasPermission('some permission')

Please sign in or create an account to participate in this conversation.