SQLSTATE[42S22]: Column not found: 1054 Unknown column 'role_not' ERROR

Question

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

Answer 1

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.