Optimization of laravel pivot table relationship

I have a pivot table called invite_riskarea which is designed as following:

This table handle the permissions that have a specific user (through an invite id) to access to specific riskfields. Each riskfield is associated to a riskarea which acts as the main container of specific riskfields.

Within the model Invite I have this relationship:

public function riskareas()
{
    return $this->belongsToMany(Riskarea::class)->withPivot('riskfield_id', 'insert', 'edit', 'view');
}

In this way I can return all the riskareas associated to a specific invite, and I should be able to return all the riskfields associated to a specific riskarea in the same invite model.

As you can see from the table invite_riskarea I have three columns called insert, edit, delete. These columns manage the types of permissions assigned to a specific user (via invite id) for a specific riskfield belonging to a riskarea.

What I'm trying to do is retrieve the riskarea permission in the following way:

$invite = Invite::where('id', 58)->first();
$riskarea = $invite->riskareas[0];
$riskfield = $riskareas->riskfields[0];
echo 'view permission => ' . $riskfield->insert;

The problem's that I'm not able to setup a correct relationship in the Invite model that return me the pivot data of the permissions columns only for the riskfield associated to the riskarea.

So I have manage to handle this situation in this way:

$riskareas = Riskarea::all();

foreach ($riskareas as &$riskarea) {
    foreach ($riskarea->riskfields as &$riskfield) {
        $result = DB::table('invite_riskarea')
            ->select('insert', 'edit', 'view')
            ->where([
                'riskarea_id' => $riskarea->id,
                'riskfield_id' => $riskfield->id
            ])
            ->first();

        if ($result) {
            $riskfield->insert = $result->insert;
            $riskfield->edit = $result->edit;
            $riskfield->view = $result->view;
        }
    }
}

Essentially, I get all the riskareas, and then I iterate over the riskfields associated. For each riskfield, I get the permissions in the invite_riskarea table and then I have the correct structure that I want.

So to summarize:

Is actually possible create a model relationship that returns the permissions for riskfield and not for riskarea?
Is my table implementation good enough to handle that situation?

sr57

4 years ago

Level 39

Nothing wrong with you schema but Laravel support only direct relation between 2 tables.

Assuming that you have probably a limited number of rows in riskareads & risksfields tables you can work with a riskafs table that is the cross join of the first tables. This cross table can be "hard coded", ie no more table or you still can implement relation one-to-many between this table and the 2 original ones. Doing this you will be able to access these data with the "Has Many Through" relation.

If you know "sql view", your cross table can be a view of the 2 original tables, doing you can work also with a large number of rows in your original tables.

Happy coding

sfarzoso

4 years ago

Level 1

@sr57 Based on what you suggested I have created a new pivot model called InviteRiskarea which contains the following:

class InviteRiskarea extends Pivot
{
    public function riskfield()
    {
        return $this->belongsTo(Riskfield::class, 'riskfield_id');
    }
}

and then, in the Invite model I changed the method riskareas as the following:

return $this->belongsToMany(Riskarea::class, 'invite_riskarea', 'invite_id', 'riskarea_id')
    ->withPivot(['riskfield_id'])
    ->using(InviteRiskarea::class);

so if I do:

$riskarea = $invite->riskareas[1];
dd($riskarea->pivot->riskfield);

I can access to the riskfield of the riskarea within the iteration, there is one more steps that I'm missing, how can I attach the extra fields (insert, edit, view) to the riskfield?

Also, I only realize now that $invite->riskareas only returns the riskareas that are registered on invite_riskarea, but is there a way to replicate the code I wrote to return all available riskareas even if they are not present on invite_riskarea table?

sr57

4 years ago

Level 39

Based on what you suggested

Base of what you understood of what I suggested :-)

Where is the table riskafs?

Maybe you should begin by redd this

https://www.sqlshack.com/sql-cross-join-with-examples/

sfarzoso

4 years ago

Level 1

@sr57 I have a pivot table called riskarea_riskfield which handle the relationship between the riskareas and all the riskfields associated to a riskarea. The main goal of the question is replicate the loop that I wrote using a Laravel relationship, but I don't know if is possible. So: retrieve all available riskareas from the riskareas table, get all the riskfields associated to a riskarea and then using a join to invite_riskarea to retrieve the available permissions for each riskfields owned by the invite id

Please or to participate in this conversation.