Tuto's Laravel Corner•5mo ago

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•5mo ago

Do you need to use stored procedures for the table itself? Or just for the create, update and delete actions?

Solution

tuto1902•5mo ago

// 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•5mo 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•5mo 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.

Gaming

Programming

How to integrate filament v3 with stored procedure