Laravel - How to get rows of permissions from another table set in roles table

Question

I am very new to laravel and trying to get the all permission rows from the permissions table that set into the roles - permission field as a json

Obviously, this is not working as aspected. It returns an array of collections. However, I want each permission rows that associate with the roles results in itself.

public function index()
{
    $roles = Role::all();

    foreach ($roles as $role) {
        $permissions[] = Permission::whereIn('id', json_decode($role->permission))->get();
    }

    dd($permissions);

}

Permissions table

Roles table

Answer 1

It is recommended to extract the permission field to a separate table. This would make it possible to implement constraints to ensure the referenced permission id and role id do actually exists. A possible table name would be: permission_role with the following columns: permission_id and role_id.

The migration could be setup like this:

Schema::create('permission_role', function (Blueprint $table) {
    $table->unsignedBigInteger('permission_id');
    $table->unsignedBigInteger('role_id');

    $table->foreign('permission_id')->references('id')->on('permissions');
    $table->foreign('role_id')->references('id')->on('roles');
});