Hi, I am new to Laravel, Filament and even PHP (but have 50 years background, last 30 years Java). Within a Filament resource I tried the following hintAction:
->hintAction(
Action::make(__('langtest.createMatchings'))
->icon('heroicon-m-star')
->requiresConfirmation()
->action(function (Set $set, $state) {
DB::table('matchings')->insertUsing([
'project_id', 'matching_person_id', 'requested_role_id'],
DB::table('projects')->select(
'projects.id', 'persons.id', 'requested_roles.id')
->join('project_states','projects.project_state_id', '=', 'project_states.id')
->where('project_states.code', '=', '\'STAFFING\'')
->where('project_states.id', '=', $state)
->join('requested_roles', 'requested_roles.project_id', '=', 'projects.id')
->where('requested_roles.discipline_id', '=', 'projects.discipline_id')
->join('roles_for_ratings', 'roles_for_ratings.discipline_id', '=', 'requested_roles.discipline_id')
->where('roles_for_ratings.discipline_role_id', '=', 'requested_roles.discipline_role_id')
->join('rating_of_relations', 'rating_of_relations.id', '=', 'roles_for_ratings.rating_id')
->join('relation_of_persons', 'relation_of_persons.id', '=', 'rating_of_relations.relation_of_persons_id')
->join('freelancers', 'freelancers.person_id', '=', 'relation_of_persons.to_person_id')
->where('freelancers.person_id', '=', 'relation_of_persons.from_person_id')
->join('persons', 'persons.id', '=', 'freelancers.person_id')
);
})
)
When activating this hintAction, I get the illuminate\Database\QueryException error message:
SQLSTATE[22007]: Invalid datetime format: 1292 Truncated incorrect DOUBLE value: 'projects.discipline_id'
The Systems shows me the resulting MySQL-Query, which I can copy and issue directly unchanged to MySQL. There it works fine without any errors or warning and with the records inserted.
Is there any internal blocking when I try to insert records in table "matchings" while this hintAction belongs to a resource of the table "projects"?
To me this tells me you are trying to insert a double into a date field.
SQLSTATE[22007]: Invalid datetime format: 1292 Truncated incorrect DOUBLE value: 'projects.discipline_id'
So I would start looking at where you use the discipline_id foriegn key.
Laravel gives me the translated SQL-Query for it:
insert into matchings (project_id, matching_person_id, requested_role_id)
select projects.id, persons.id, requested_roles.id from projects
inner join project_states on projects.project_state_id = project_states.id
inner join requested_roles on requested_roles.project_id = projects.id
inner join roles_for_ratings on roles_for_ratings.discipline_id = requested_roles.discipline_id
inner join rating_of_relations on rating_of_relations.id = roles_for_ratings.rating_id
inner join relation_of_persons on relation_of_persons.id = rating_of_relations.relation_of_persons_id
inner join freelancers on freelancers.person_id = relation_of_persons.to_person_id
inner join persons on persons.id = freelancers.person_id
where project_states.code = 'STAFFING' and project_states.id = 10
and requested_roles.discipline_id = projects.discipline_id
and roles_for_ratings.discipline_role_id = requested_roles.discipline_role_id
and freelancers.person_id = relation_of_persons.from_person_id;
This SQL-query executes perfectly without any errors or warnings with successfully inserted records. It does not store any values except for the 3 IDs.
Checking the error message (without the discipline_id) in the web gives many entries with what looks to me as a typical exception message independent of the mentioned fields.
@fw-Ahr Wild guess here, you are not using integers as your primary keys, if so then take a look here.
Thank you for your hint. I checked all values, they are integers, not NULL. If I fix the value of discipline.id then the same message comes up with discipline_role.id.
If I look into the LOG then I see this as the first lines:
[2024-03-01 09:46:14] local.ERROR: SQLSTATE[22007]: Invalid datetime format: 1292 Truncated incorrect DOUBLE value: 'disciplines.id' (Connection: mysql, SQL: insert into matchings (project_id, matching_person_id, requested_role_id) select projects.id, persons.id, requested_roles.id from projects inner join project_states on projects.project_state_id = project_states.id inner join disciplines on disciplines.id = projects.discipline_id inner join requested_roles on requested_roles.project_id = projects.id inner join discipline_roles on discipline_roles.id = requested_roles.discipline_role_id inner join roles_for_ratings on roles_for_ratings.discipline_id = disciplines.id inner join rating_of_relations on rating_of_relations.id = roles_for_ratings.rating_id inner join relation_of_persons on relation_of_persons.id = rating_of_relations.relation_of_persons_id inner join freelancers on freelancers.person_id = relation_of_persons.to_person_id inner join persons on persons.id = freelancers.person_id where project_states.code = 'STAFFING' and project_states.id = 10 and requested_roles.discipline_id = disciplines.id and roles_for_ratings.discipline_role_id = discipline_roles.id and freelancers.person_id = relation_of_persons.from_person_id) {"userId":1,"exception":"[object] (Illuminate\Database\QueryException(code: 22007): SQLSTATE[22007]: Invalid datetime format: 1292 Truncated incorrect DOUBLE value: 'disciplines.id' (Connection: mysql, SQL: insert into matchings (project_id, matching_person_id, requested_role_id) select projects.id, persons.id, requested_roles.id from projects inner join project_states on projects.project_state_id = project_states.id inner join disciplines on disciplines.id = projects.discipline_id inner join requested_roles on requested_roles.project_id = projects.id inner join discipline_roles on discipline_roles.id = requested_roles.discipline_role_id inner join roles_for_ratings on roles_for_ratings.discipline_id = disciplines.id inner join rating_of_relations on rating_of_relations.id = roles_for_ratings.rating_id inner join relation_of_persons on relation_of_persons.id = rating_of_relations.relation_of_persons_id inner join freelancers on freelancers.person_id = relation_of_persons.to_person_id inner join persons on persons.id = freelancers.person_id where project_states.code = 'STAFFING' and project_states.id = 10 and requested_roles.discipline_id = disciplines.id and roles_for_ratings.discipline_role_id = discipline_roles.id and freelancers.person_id = relation_of_persons.from_person_id) at /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php:829)
[stacktrace]
#0 /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php(783): Illuminate\Database\Connection->runQueryCallback()
#1 /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php(600): Illuminate\Database\Connection->run()
#2 /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(3403): Illuminate\Database\Connection->affectingStatement()
#3 /var/www/html/app/Filament/Resources/ProjectResource.php(73): Illuminate\Database\Query\Builder->insertUsing()
#4 /var/www/html/vendor/filament/support/src/Concerns/EvaluatesClosures.php(35): App\Filament\Resources\ProjectResource::App\Filament\Resources\{closure}()
#5 /var/www/html/vendor/filament/actions/src/MountableAction.php(39): Filament\Support\Components\Component->evaluate()
...
It shows the SQL, which I can send directly to MySQL and I receive this:
mysql> insert into matchings (project_id, matching_person_id, requested_role_id) select projects.id, persons.id, requested_roles.id from projects inner join project_states on projects.project_state_id = project_states.id inner join disciplines on disciplines.id = projects.discipline_id inner join requested_roles on requested_roles.project_id = projects.id inner join discipline_roles on discipline_roles.id = requested_roles.discipline_role_id inner join roles_for_ratings on roles_for_ratings.discipline_id = disciplines.id inner join rating_of_relations on rating_of_relations.id = roles_for_ratings.rating_id inner join relation_of_persons on relation_of_persons.id = rating_of_relations.relation_of_persons_id inner join freelancers on freelancers.person_id = relation_of_persons.to_person_id inner join persons on persons.id = freelancers.person_id where project_states.code = 'STAFFING' and project_states.id = 10 and requested_roles.discipline_id = disciplines.id and roles_for_ratings.discipline_role_id = discipline_roles.id and freelancers.person_id = relation_of_persons.from_person_id;
Query OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql>
Which means: somewhere inside the sending of Laravel/Filament we have the problem. Without DEBUG I get the server error 500 and the LOG-entry is the same.
In addition: If I have DEBUG activated and I look at the DEBUG-Response, there is a piece of text with the heading "Updates" and it shows:
Something went wrong in Ignition!
An error occurred in Ignition's UI. Please open an issue on the Ignition GitHub repo and make sure to include any errors or warnings in the developer console.
Since I am new to Laravel and Filament, I do not understand, what that means.
There must be some kind of limitation inside Laravel/Filament, which prevents me from doing an INSERT into the model X while I am active inside the resource of model Y.
I found a way to do it:
->action(function (Set $set, $state) {
// since laravel / filament somehow blocks the execution, we do it the raw way and it works
$insertions = DB::select('select projects.id as project, persons.id as person, requested_roles.id as role
from projects
inner join project_states on projects.project_state_id = project_states.id
inner join requested_roles on requested_roles.project_id = projects.id
inner join roles_for_ratings on roles_for_ratings.discipline_id = requested_roles.discipline_id
inner join rating_of_relations on rating_of_relations.id = roles_for_ratings.rating_id
inner join relation_of_persons on relation_of_persons.id = rating_of_relations.relation_of_persons_id
inner join freelancers on freelancers.person_id = relation_of_persons.to_person_id
inner join persons on persons.id = freelancers.person_id
where project_states.code = "STAFFING"
and project_states.id = ?
and requested_roles.discipline_id = projects.discipline_id
and roles_for_ratings.discipline_role_id = requested_roles.discipline_role_id
and freelancers.person_id = relation_of_persons.from_person_id', [$state]);
foreach ($insertions as $insertit) {
$matching = new Matching;
$matching->project_id = $insertit->project;
$matching->matching_person_id = $insertit->person;
$matching->requested_role_id = $insertit->role;
$matching->save();
}
})
It is raw SQL to generate the record-values and later on make separate inserts into the related model.
But how did Laravel / Filament prevent the insertion in the first place?
Please or to participate in this conversation.