Tuto's Laravel Corner•12mo 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

5 Replies

tuto1902•12mo ago

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

Solution

tuto1902•12mo 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•12mo 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 REKTOP•11mo 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.

Jonathan•5mo ago

I have a stored procedure that returns the data to display (they are related in several tables) and I would like to use that result in the table to display for a resource, how should I implement it?

Gaming

Programming

How to integrate filament v3 with stored procedure