SQLSTATE[42S22]: Column not found: 1054 Unknown column 'roles.user_id' in 'where clause'

Question

I've been getting the following error when trying to attach or retrieve "roles" from "users".

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'roles.user_id' in 'where clause' (SQL: select * from roles where roles.user_id = 1 and roles.user_id is not null limit 1)

table

public function up()
    {
        Schema::create('roles', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name', 50);
            $table->timestamps();
        });
    }

Role.php

class Role extends Model
{
    protected $fillable = ['name'];
    public $timestamps = false;

    public function users()
    {
        return $this->hasMany(User::class);
    }
}

User.php

class User extends Authenticatable
{
    use Notifiable;

    protected $table = 'users';

    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = [
        'role_id', 'username', 'firstname', 'lastname', 'active', 'email', 'password',
    ];

    /**
     * The attributes that should be hidden for arrays.
     *
     * @var array
     */
    protected $hidden = [
        'password', 'remember_token',
    ];

    public function role()
    {
        return $this->hasOne(Role::class);
    }

    private function checkIfUserHasRole($need_role)
    {
        return (strtolower($need_role) == strtolower($this->role->name)) ? true : null;
        return $need_role;
    }

    public function hasRole($roles)
    {
        if (is_array($roles))
        {
            foreach ($roles as $need_role)
            {
                if ($this->checkIfUserHasRole($need_role))
                {
                    return true;
                }
            }
        }
        else
        {
            return $this->checkIfUserHasRole($roles);
        }
        return false;
    }
}

Middleware/CheckRole.php

class CheckRole
{
    /**
     * Handle an incoming request.
     *
     * @param  \Illuminate\Http\Request  $request
     * @param  \Closure  $next
     * @return mixed
     */
    public function handle($request, Closure $next)
    {
        $roles = $this->getRequiredRoleForRoute($request->route());
        if ($request->user()->hasRole($roles) || !$roles){
            return $next($request);
        }
        return redirect()->route('noPermissions');
    }

    private function getRequiredRoleForRoute($route)
    {
        $actions = $route->getAction();
        return isset($actions['roles']) ? $actions['roles'] : null;
    }
}

web.php

Route::get('/', ['as' => '/', 'uses' => 'LoginController@getLogin']);
Route::post('/login', ['as' => 'login', 'uses' => 'LoginController@postLogin']);

Route::group(['middleware' => ['authenticates', 'checkrole']], function (){
    Route::get('/logout', ['as' => 'logout', 'uses' => 'LoginController@getLogout']);
    Route::get('/dashboard', ['as' => 'dashboard', 'uses' => 'DashboardController@dashboard']);
});

Answer 1

In the User class, you are using the wrong relation-method. Try changing

public function role()
{
    return $this->hasOne(Role::class);
}

into

public function role()
{
    return $this->belongsTo(Role::class);
}

as the documentation says. hasOne expects the offending roles.user_id to exist.