Optimization of laravel pivot table relationship

Question

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

Entity Relationship Diagram

This table stores the permissions that have a specific user (through an invite id) to give access to specific riskfields. Each riskfield is associated with 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 with a specific invite, and I should be able to return all the riskfields associated with 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 is that I'm not able to setup a correct relationship in the Invite model that returns the pivot data of the permissions columns only for the riskfield associated to the riskarea.

So I have managed 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:

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

• user3402600: Have you thought about creating an InviteRiskarea model? This model can be used as a starting point to filter out all other data. You could also use Eager loading to improve performance. I suspect that your current loop is expensive to run.
• sfarzoso: @user3402600 I'm thinking another way and I would like to have your opinion about that. What about if I retrieve all the riskareas and then when I iterate over the riskfields associated to the riskarea, I check if the riskfield has a permission querying the table invite_riskfield? Do you think it's a good approach? It would be similar to the hasRole method of laratrust package. I just need to figure out how to implement this, may you could give me an help?

Answer 1

Your questions

It appears the answer by Luidmila to your question on stack overflow was helpful. The callback to the whereHas could be simplified using an arrow function as long as the server is running PHP 7.4 or newer (which is hopefully the case given LTS). Doing this would allow the elimination of the use statement for $invite_id.

Review

There isn't much code here to review but below are some critiques.

Simpler method to find a model instance

While it may just be sample code, the code contains this line:

$invite = Invite::where('id', 58)->first();

The code for the invite model was not included but the entity relationship diagram depicts that the id field is the primary key. One could use the find() method instead of where to simplify finding the invitation by the id field. There are also helper methods like findOrFail which may be helpful in certain scenarios.

repeated lines to set values from result

In the example code there are also these three lines

$riskfield->insert = $result->insert;
$riskfield->edit = $result->edit;
$riskfield->view = $result->view;

Bearing in mind that these lines may not be needed anymore, there may be other places where the pattern occurs.

In such a case, a loop could be used to simplify these lines - especially if four or more properties need to be assigned:

foreach (['insert', 'edit', 'view'] as $property) {
 $riskfield->$property = $result->$property;
}