Optimization of Laravel pivot table relationship

Question

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

This table handles the permissions that have a specific user (through an invite id) to 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, and delete. These columns manage the types of permissions assigned to a specific user (via an invite id) for a specific riskfield belonging to a riskarea.

I'm trying to 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 returns me the pivot data of the permissions columns only for the riskfield associated with 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:

1. Is it actually possible 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?

Answer 1

1. I suggest you define back the many-to-many relation for the Riskfield model with the Invite model.

   You can also define a direct many-to-many relationship with riskfield in the Invite model. This is how convenient it is for you personally.

   And so the inverse many-to-many relationship

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

   Then get all objects' Riskfields that are associated with the specified invite:

    $riskfields = Riskfields::wherehas('invites' . function (Builder $query) use ($invite_id) {
        $query->where('invites.id', $invite_id);
    })->with('invites')->get();
   

   Then you can access the desired fields of the pivot table in the specified way:

    foreach ($riskfields as $riskfield) {
        foreach ($riskfield->invites as $invite) {
            $insertRiskField = $invite->pivot->insert;
            $editRiskField = $invite->pivot->edit;
            $viewRiskField = $invite->pivot->view;
        }
    }
   

   Eager loading executes one query to the database

2. Yes

   Documentation Laravel