Level 55
@warpig the problem is that you role_id is not assigned during query. Can you show your controller/service when you perform inserting?
May 12, 2021
11
Level 12
Integrity constraint violation: Column "role_id" cannot be null
I want to be able to assign an ability to a role through the front end, but right now im encountering this issue:
SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'role_id' cannot be null (SQL: insert into `ability_role` (`ability_id`, `created_at`, `role_id`, `updated_at`) values (2, 2021-05-13 00:58:39, ?, 2021-05-13 00:58:39))
I have 3 tables and each of these they relate to one another, but ability_role is where I can associate an ability to a role;
- Abilities
- Roles
- Ability_role
I am using this method
public function assignAbility(Request $request, Role $role, Ability $abilities)
{
foreach ($request->abilities as $ability) {
$role->allowTo($ability);
}
return redirect()->back();
}
To receive the request sent from this form element below, I have to send the value of the ID inside of the value parameter of the <option> tag, and it can accept an array if needed.
<form
action="{{ route('assignAbility') }}"
method="POST"
> @csrf
<select
name="abilities[]"
id="abilities"
multiple
>
@foreach ($abilities as $ability)
<option value="{{ $ability->id }}">
{{ $ability->name }}
</option>
@endforeach
</select>
<button type="submit">
Assign
</button>
</form>
When I submit the form I get this message:
SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'role_id' cannot be null (SQL: insert into `ability_role` (`ability_id`, `created_at`, `role_id`, `updated_at`) values (2, 2021-05-13 00:58:39, ?, 2021-05-13 00:58:39))
How could I consider the role_id column on the method? Thanks.
Level 12
Hey @silencebringer, sure here goes:
<?php
namespace App\Http\Controllers;
use App\Models\Role;
use App\Models\Ability;
use Illuminate\Http\Request;
class AbilitiesRoleController extends Controller
{
public function assignAbility(Request $request, Role $role, Ability $abilities)
{
foreach ($request->abilities as $ability) {
$role->allowTo($ability);
}
return redirect()->back();
}
}
Level 55
@warpig do this
public function assignAbility(Request $request, Role $role, Ability $abilities)
{
dd($role);
}
looks like $role is new role, not existing one. Do you have it from url string as model binding?
Level 12
No, I get this:
App\Models\Role {#1450 ▼
#fillable: array:2 [▼
0 => "name"
1 => "label"
]
#connection: null
#table: null
#primaryKey: "id"
#keyType: "int"
+incrementing: true
#with: []
#withCount: []
#perPage: 15
+exists: false
+wasRecentlyCreated: false
#attributes: []
#original: []
#changes: []
#casts: []
#classCastCache: []
#dates: []
#dateFormat: null
#appends: []
#dispatchesEvents: []
#observables: []
#relations: []
#touches: []
+timestamps: true
#hidden: []
#visible: []
#guarded: array:1 [▼
0 => "*"
]
}
Level 54
why no id? how the schema look like?
Level 12
how the schema look like?
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class CreateRolesTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('roles', function (Blueprint $table) {
$table->bigIncrements('id');
$table->string('name');
$table->string('label')->nullable();
$table->timestamps();
});
Schema::create('abilities', function (Blueprint $table) {
$table->bigIncrements('id');
$table->string('name');
$table->string('label')->nullable();
$table->timestamps();
});
Schema::create('ability_role', function (Blueprint $table) {
$table->primary(['role_id', 'ability_id']);
$table->unsignedBigInteger('role_id');
$table->unsignedBigInteger('ability_id');
$table->timestamps();
$table->foreign('role_id')
->references('id')
->on('roles')
->onDelete('cascade');
$table->foreign('ability_id')
->references('id')
->on('abilities')
->onDelete('cascade');
});
Schema::create('role_user', function (Blueprint $table) {
$table->primary(['user_id', 'role_id']);
$table->unsignedBigInteger('user_id');
$table->unsignedBigInteger('role_id');
$table->timestamps();
$table->foreign('user_id')
->references('id')
->on('users')
->onDelete('cascade');
$table->foreign('role_id')
->references('id')
->on('roles')
->onDelete('cascade');
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::table('roles', function (Blueprint $table) {
Schema::dropIfExists('roles');
});
}
}
what is inside allowTo()?
public function allowTo($ability)
{
$this->abilities()->sync($ability, false);
}
Level 54
how about try replace:
foreach ($request->abilities as $ability) {
$role->allowTo($ability);
}
with
$role->allowTo($abilities);
Level 54
what is inside allowTo()? why not use ->attach()?
Level 12
why no id?
Could be very insightful if you could provide an example of what you mean? I understood it as, why I wouldn't grab the id of a role first? If that assumption is correct then this is how i've tried grabbing that value:
$role = Role::where('id', $role)->get();
why not use ->attach()?
I believe I am not using attach because if an ability gets deleted then the record would stay instead of being removed, it's also on the schema but don't quote me 100% on that, that's the best I can remember from Jeffreys code:
$table->foreign('role_id')
->references('id')
->on('roles')
->onDelete('cascade');
$table->foreign('ability_id')
->references('id')
->on('abilities')
->onDelete('cascade');
Level 12
Ok @silencebringer did you meant something like this?
web.php
Route::post('role/{role}/assignAbility', [AbilitiesRoleController::class, 'assignAbility']);
This gets me an URI like this: http://videotheque.test/admin/role/2/assignAbility
The form now has this on the action: action="role/{{$role->id}}/assignAbility"
If I dd($role) on my method now I get this:
App\Models\Role {#1460 ▼
#fillable: array:2 [▶]
#connection: "mysql"
#table: "roles"
#primaryKey: "id"
#keyType: "int"
+incrementing: true
#with: []
#withCount: []
#perPage: 15
+exists: true
+wasRecentlyCreated: false
#attributes: array:5 [▼
"id" => 2
"name" => "moddy"
"label" => null
"created_at" => "2021-05-11 01:24:11"
"updated_at" => "2021-05-11 01:24:11"
]
#original: array:5 [▶]
#changes: []
#casts: []
#classCastCache: []
#dates: []
#dateFormat: null
#appends: []
#dispatchesEvents: []
#observables: []
#relations: []
#touches: []
+timestamps: true
#hidden: []
#visible: []
#guarded: array:1 [▶]
}
Level 55
@warpig yes, now your role exists
+exists: true
and you should be able to call the methods. Is it works now?
Please or to participate in this conversation.