SQLSTATE[42S22]: Column Not Found: 1054 Unknown Column 'role_not' ERROR

- 1 answer

I am trying to display users other than super_admin so I used whereRoleNot Function in my controller to hide super_admin . My User Model:

public function scopeWhereRole($query, $role_name){
    return $query->whereHas('roles', function($q) use($role_name){
        return $q->whereIn('name', (array)'$role_name');
}  //end of scopeWhereRole

public function scopeWhereRoleNotIn($query, $role_name){
    return $query->whereHas('roles', function($q) use($role_name){
        return $q->whereNotIn('name', (array)'$role_name');
}  //scopeWhereRoleNotIn end

And User controller index method:

public function index()

     $users= User::whereRoleNot('super_admin')->paginate(3);
     return view('dashboard.users.index', compact('users'));
 }  //end of index


The core issue is that you had a typo when using your scope, but due to the way you named your scope, it was still a valid where{column} clause.

Laravel has dynamic functions, like where{column}($search) that constructs a simple where clause for the {column} value and $search value provided. Take your example:

$users = User::whereRoleNot('super-admin');

whereRoleNot tries to create a where clause for the column, in your case, role_not (dynamically constructed from the string RoleNot), and your database table does not have this column.

Simply use a normal where clause:

$users = User::where('role', '!=', 'super_admin')->paginate(3);

Edit: If you want to use a scope, I'd suggest you change the name a bit:

public function scopeRoleIn($query, $role_name){
  return $query->whereHas('roles', function($q) use($role_name){
    return $q->whereIn('name', (array)$role_name); // Don't use `'` here

public function scopeRoleNotIn($query, $role_name){ 
  return $query->whereHas('roles', function($q) use($role_name){
    return $q->whereNotIn('name', (array)$role_name); // Don't use `'` here

Then, use your scope as follows:

// $users = User::roleIn('super-admin')->paginate(3); // Etc...
$users = User::roleNotIn('super-admin')->paginate(3);

You may use scopeWhere..., but that naming potentially conflicts with Laravel's dynamic where{column} clauses, so you should avoid it.