Level 29
Can you show your codes?
- the migration files and name
- the models
Or
A quick way to fix it is setting the table name
Aug 31, 2021
7
Level 5
Pivot Table Naming Issue
Issue (Example):
SQLSTATE[42S02]: Base table or view not found: 1146 Table 'role_users' doesn't exist (SQL: select count(*) as aggregate from role_users where role_id = 4)
The 3 tables are: roles, role_user, users
My understanding from the Laravel 8.x documentation is that "role_user" is the correct name to use. It works in most circumstances and is looked for correctly as "role_user".
Example of where I see it failing and looking for "role_users": RoleUser::where( 'role_id' , $role -> id ) -> count ( ) ; ^ This yields the message above. But why is it looking for "role_users" instead of "role_user"?
Open to thoughts, suggestions, etc.
Side note: 'grep -rai "role_users" ' yields no results (except those in the laravel.log file from the crashes).
Level 5
This would indeed make it work. However, it would be considered a patch, not a fix.
Level 29
Can you show your codes?
- the migration files and name
- the models
Example
public function roles(){
return $this->belongsToMany(User::class, 'role_user', 'id', 'user_id');
}
1 like
Level 5
It is exactly like the documentation: https://laravel.com/docs/8.x/eloquent-relationships#many-to-many-model-structure
public function roles()
{
return $this->belongsToMany(Role::class);
}
Level 55
@rsecor https://laravel.com/docs/8.x/eloquent#table-names
By convention, the "snake case", plural name of the class will be used as the table name unless another name is explicitly specified.
so, for RoleUser default table name will be role_users
if you use custom name you need to specify it explicitly
class RoleUser extends Model
{
protected $table = 'role_user';
}
But really I think you use pivot table wrong. Usually you do not need separated model class for pivot table. If you will define belongsToMany relationship for both - users and roles - you do not need RoleUser at all. Read more here https://laravel.com/docs/8.x/eloquent-relationships#many-to-many
Level 5
The documentation is a little hard to follow. Thanks for the information. I changed the table (etc) back to role_users, etc.
Now I get...
SQLSTATE[42S02]: Base table or view not found: 1146 Table 'role_user' doesn't exist (SQL: select roles.*, role_user.user_id as pivot_user_id, role_user.role_id as pivot_role_id from roles inner join role_user on roles.id = role_user.role_id where role_user.user_id = 1)
This is why I had force changed it ot role_users in the first place.
Thoughts?
Level 55
@rsecor if you changed relationships to belongsToMany - your pivot table name (following conventions) should be role_user https://laravel.com/docs/8.x/eloquent-relationships#many-to-many-table-structure
The role_user table is derived from the alphabetical order of the related model names
in User model
public function roles()
{
return $this->belongsToMany(Role::class);
}
in Role model
public function users()
{
return $this->belongsToMany(User::class);
}
if you want to use cutome pivot table name - just pass it as second param to relationship declaration
public function roles()
{
return $this->belongsToMany(Role::class. 'role_users');
}
public function users()
{
return $this->belongsToMany(User::class, 'role_users');
}
Please or to participate in this conversation.