I am working on a Laravel 8 app with users, roles and permissions.
I try to get the current user’s list of permissions.
In appModelsPermission.php
I have:
class Permission extends Model { use HasFactory; protected $fillable = [ 'name', 'list' ]; }
In appModelsRole.php
I have:
class Role extends Model { use HasFactory; protected $fillable = [ 'name', 'permission_id' ]; }
In the controller, I first get the user’s role id:
$user_role = User::where('email', $user->getMail())->first()->role_id;
Then, the list of permissions:
$user_permissions = Permission::select ( "roles.name as role_name", "permissions.list as permissions_list", ) ->leftJoin("permissions", "roles.permission_id", "=", "permissions.id") ->where('roles.id', $user_role) ->get();
The above is intended to be the equivalent of this MySQL query, that works:
SELECT roles.name as role_name, permissions.list AS permissions_list FROM roles LEFT JOIN permissions ON roles.permission_id = permissions.id WHERE roles.id = 1
The problem
The Laravel query builder you can see above fails with this message:
Syntax error or access violation: 1066 Not unique table/alias: 'permissions' (SQL: select `roles`.`name`, `permissions`.`list` from `permissions` left join `permissions` on `roles`.`permission_id` = `permissions`.`id` where `roles`.`id` = 1)
I can’t understand why, since I have tested the MySQL query in phpMyAdmin and it works.
Where is my mistake?
Advertisement
Answer
->leftJoin("permissions", "roles.permission_id", "=",
"permissions.id")
should be
->leftJoin("roles", "roles.permission_id", "=", "permissions.id")