OunknownO OunknownO - 8 days ago 8
MySQL Question

how to access pivot table column

I have bican roles plugin and I need to access over pivot table. This is in my controller code

$users = User::where('role_id','=',2)->get();


migration roles

Schema::create('roles', function (Blueprint $table) {
$table->increments('id')->unsigned();
$table->string('name');
$table->string('slug')->unique();
$table->string('description')->nullable();
$table->integer('level')->default(1);
$table->timestamps();
});


migration user

Schema::create('users', function (Blueprint $table) {
$table->increments('id');
$table->string('name')->unique();
$table->string('email')->unique();
$table->rememberToken();
$table->timestamps();
});


pivot migration

Schema::create('role_user', function (Blueprint $table) {
$table->increments('id')->unsigned();
$table->integer('role_id')->unsigned()->index();
$table->foreign('role_id')->references('id')->on('roles')->onDelete('cascade');
$table->integer('user_id')->unsigned()->index();
$table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
$table->timestamps();
});

Answer

If your relation between user and role is many-to-many then it looks that you are querying User incorrectly. The right query will be as:

$users = User::whereHas('roles', function($q) {
                    $q->where('role_id', 2);
              })
              ->get();

Then you can get user's role as:

foreach($users as $user) {
    $user->roles; // returns collection of roles
}
Comments