How can I get this many-to-many relationship work in Laravel 5.5?

Question

I am getting an error with a many to many relationship in eloquent in Laravel 5.5

Illuminate \ Database \ QueryException (42000) SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'users' (SQL: select users.*, users.roles_id as pivot_roles_id, users.users_id as pivot_users_id, users.id as pivot_id, users.created as pivot_created, users.updated as pivot_updated, users.deleted as pivot_deleted from users inner join users on users.id = users.users_id where users.roles_id = 2)

I have a users table, roles table and a user_role table (pivot table). The user_role table has the following columns: id, users_id(fk from users table),roles_id (fk from roles table),created,updated,deleted.

In the User model I have

public function roles(){
    return $this->belongsToMany(\App\roles::class,'roles','users_id','roles_id','id','id')->withPivot(['id','created','updated','deleted']);
}

In the roles model I have

public function users(){
    return $this->belongsToMany(\App\User::class,'users','roles_id','users_id','id','id')->withPivot(['id','created','updated','deleted']);
}

In my controller I have

$roles = $this->rolesObject->whereNull('deleted')->orderBy('role')->get();
//Just for testing how to view the relationship won't be real code in the end
foreach($roles as $role){
    foreach($role->users as $user){
        dump($user->display_name);
    }
}

Looking through the documentation and it looks like i am setting up the relationship correctly but obviously I'm not and I have no idea what I am doing wrong

Answer 1

The second parameter to belongsToMany method is a relation table name(docs), you have to pass role_user rather than users.

public function roles(){
    return $this->belongsToMany(\App\roles::class,'role_user','users_id','roles_id','id','id')->withPivot(['id','created','updated','deleted']);
}

...
public function users(){
    return $this->belongsToMany(\App\User::class,'role_user','roles_id','users_id','id','id')->withPivot(['id','created','updated','deleted']);
}

Your code thinks that your second parameter users is a relation table name, that's the reason of the error.