How to integrate filament v3 with stored procedure

I want to use stored procedures for CRUD needs. But I'm confused about how to call the stored procedure function from the create/edit button, or when I want to display the resulting data from the stored procedure in a table. anyone can help ?
Solution:
I think I'm going to record a video about this concept. Do you mind if I include your question in the video?(including your discord user name). Here's an example of using stored procedures to manage the users in a filament resource. First the create action ```php // app/Filament/Resources/Pages/CreateUser.php class CreateUser extends CreateRecord...
Jump to solution
4 Replies
tuto1902
tuto19027mo ago
Do you need to use stored procedures for the table itself? Or just for the create, update and delete actions?
Solution
tuto1902
tuto19027mo ago
I think I'm going to record a video about this concept. Do you mind if I include your question in the video?(including your discord user name). Here's an example of using stored procedures to manage the users in a filament resource. First the create action
// app/Filament/Resources/Pages/CreateUser.php
class CreateUser extends CreateRecord
{
protected static string $resource = UserResource::class;

protected function handleRecordCreation(array $data): Model
{
// Execute the stored procedure with the form data. The @last_id is expected to hold
// the last inserted ID from the database
DB::statement('CALL sp_insert_user(?, ?, ?, @last_id)', [$data['name'], $data['email'], $data['password']]);

// Grab the last inserted ID
$last_id = DB::scalar('SELECT @last_id');

// Load the newly created record from the database
$user = User::find($last_id);

return $user;
}
}
// app/Filament/Resources/Pages/CreateUser.php
class CreateUser extends CreateRecord
{
protected static string $resource = UserResource::class;

protected function handleRecordCreation(array $data): Model
{
// Execute the stored procedure with the form data. The @last_id is expected to hold
// the last inserted ID from the database
DB::statement('CALL sp_insert_user(?, ?, ?, @last_id)', [$data['name'], $data['email'], $data['password']]);

// Grab the last inserted ID
$last_id = DB::scalar('SELECT @last_id');

// Load the newly created record from the database
$user = User::find($last_id);

return $user;
}
}
tuto1902
tuto19027mo ago
Here's the sp_insert_user definition
CREATE PROCEDURE sp_insert_user(
IN _name VARCHAR(255),
IN _email VARCHAR(255),
IN _password VARCHAR(255),
OUT last_id INT
)
BEGIN
INSERT INTO users (name, email, password)
VALUES (_name, _email, _password);

SET last_id = LAST_INSERT_ID();
END
CREATE PROCEDURE sp_insert_user(
IN _name VARCHAR(255),
IN _email VARCHAR(255),
IN _password VARCHAR(255),
OUT last_id INT
)
BEGIN
INSERT INTO users (name, email, password)
VALUES (_name, _email, _password);

SET last_id = LAST_INSERT_ID();
END
Then, the edit action
class EditUser extends EditRecord
{
protected static string $resource = UserResource::class;

protected function getHeaderActions(): array
{
return [
Actions\DeleteAction::make()
];
}

protected function handleRecordUpdate(Model $record, array $data): Model
{
// Execute the stpred procedure with the form data and the record id
DB::statement('CALL sp_update_user(?, ?, ?)', [$record->getKey(), $data['name'], $data['email']]);

// Refresh the record with up to date information from the database
$record->refresh();

return $record;
}
}
class EditUser extends EditRecord
{
protected static string $resource = UserResource::class;

protected function getHeaderActions(): array
{
return [
Actions\DeleteAction::make()
];
}

protected function handleRecordUpdate(Model $record, array $data): Model
{
// Execute the stpred procedure with the form data and the record id
DB::statement('CALL sp_update_user(?, ?, ?)', [$record->getKey(), $data['name'], $data['email']]);

// Refresh the record with up to date information from the database
$record->refresh();

return $record;
}
}
here's the sp_update_user definition
CREATE PROCEDURE sp_update_user(
IN _id BIGINT,
IN _name VARCHAR(255),
IN _email VARCHAR(255)
)
BEGIN
UPDATE users SET name = _name, email = _email
WHERE id = _id;
END
CREATE PROCEDURE sp_update_user(
IN _id BIGINT,
IN _name VARCHAR(255),
IN _email VARCHAR(255)
)
BEGIN
UPDATE users SET name = _name, email = _email
WHERE id = _id;
END
And finally the delete action. For this, use the ->action() method in the DeleteAction from the getHeaderActions() function on the EditUser class
class EditUser extends EditRecord
{
protected static string $resource = UserResource::class;

protected function getHeaderActions(): array
{
return [
Actions\DeleteAction::make()
->action(function (User $record, Actions\DeleteAction $action) {
// Execute the stored procedure
DB::statement('CALL sp_delete_user(?)', [$record->id]);

// Call the action's success method to send a notification to the
// user and redirect back to the resource list page. Otherwise,
// you'll stay on the same page and receive a 404 exception
return $action->success();
}),
];
}

protected function handleRecordUpdate(Model $record, array $data): Model
{
...
}
}
class EditUser extends EditRecord
{
protected static string $resource = UserResource::class;

protected function getHeaderActions(): array
{
return [
Actions\DeleteAction::make()
->action(function (User $record, Actions\DeleteAction $action) {
// Execute the stored procedure
DB::statement('CALL sp_delete_user(?)', [$record->id]);

// Call the action's success method to send a notification to the
// user and redirect back to the resource list page. Otherwise,
// you'll stay on the same page and receive a 404 exception
return $action->success();
}),
];
}

protected function handleRecordUpdate(Model $record, array $data): Model
{
...
}
}
As for the table itself. I don't think you should use a stored procedure to display the resulting data in a table. Reading from a table should not be restricted. And if, for some reason it is, the right way would be to create a database view that we can read from as if it was a normal table.
EL REKT
EL REKT6mo ago
Wow! Thank you for helping, even making a video. I have resolved the problem, but I just found out that you responded to the question and your video was on my YouTube.. Thank you very much for helping.