Pivot tables with an extra foreign key

Hello @all,

I have a problem to use pivot tables with an extra foreign key. The situation is as follows:

I have 4 tables Table 1: risks Table 2: riskstatuses Table 3: users Table 4: risk_risksstatus

The following relationships exist:

A risk can have one or more riskstatuses.
A riskstatus can have one or more risks. --> risks to riskstatuses (n:m)
A User can assign one or more riskstatuses to one or more risks.
One riskstatus to one risk can only be assigned by one user. --> users to risk_riskstatus (n:1)

Therefore, I have created the following pivot table:

Schema::create('risk_riskstatus', function (Blueprint $table) {
            $table->bigIncrements('id');

            $table->timestamps();

            $table->unsignedBigInteger('risk_id');
            $table->foreign('risk_id')->references('id')->on('risks')->onDelete('cascade');

            $table->unsignedBigInteger('riskstatus_id');
            $table->foreign('riskstatus_id')->references('id')->on('riskstatuses');

            $table->unsignedBigInteger('user_id');
            $table->foreign('user_id')->references('id')->on('users');

            $table->unique(['risk_id', 'riskstatus_id', 'user_id']);
	}

Now, when I want to query which user has set the status, I do not get any response.

 App\Risk::find(3)->riskstatus
=> Illuminate\Database\Eloquent\Collection {#4086

     all: [

       App\Riskstatus {#4078
         id: 4,
         name: "Rsik accepted",
         created_at: "2020-07-23 19:29:34",
         updated_at: "2020-07-23 19:29:34",
         pivot: Illuminate\Database\Eloquent\Relations\Pivot {#4083
           risk_id: 3,
           riskstatus_id: 4,
         },
       },
     ],
   }

My expectation was that this query would also give me the user who assigned the risk status to that risk. Unfortunately, this is not the case. What could be the reason for this? Where is my mistake here?

Thanks

Many greetings Matthias

SilenceBringer

5 years ago

Best Answer

Level 55

Hi @riskmate_mw

From the relation documentation Many To Many, subsection Retrieving Intermediate Table Columns

By default, only the model keys will be present on the pivot object. If your pivot table contains extra attributes, you must specify them when defining the relationship

So, in your case, you should declare relation in your Risk model like this:

public function riskstatus() { return $this->belongsToMany('App\Riskstatus')->withPivot('user_id') }

So, you result will looks like

App\Risk::find(3)->riskstatus => Illuminate\Database\Eloquent\Collection {#4086 all: [ App\Riskstatus {#4078 id: 4, name: "Rsik accepted", created_at: "2020-07-23 19:29:34", updated_at: "2020-07-23 19:29:34", pivot: Illuminate\Database\Eloquent\Relations\Pivot {#4083 risk_id: 3, riskstatus_id: 4, user_id: <userIdHere> }, }, ], }

By the way, you still not be able to get user name directly here (by using eager loading). You'll need to search in users table by id manually.

Otherwise, if you want to be able to use eager load for all 3 values, you need to create general table for this relation (not the pivot table) and define appropriaterelations

riskmate_mw
OP
5 years ago

Level 1

Hello, SilenceBringer,

Thank you for the two tips. I have decided to continue with the pivot for now. Your advice works as described.

=> Illuminate\Database\Eloquent\Collection {#4096 all: [ App\Riskstatus {#4088 id: 4, name: "Rsik accepted", created_at: "2020-07-24 07:00:08", updated_at: "2020-07-24 07:00:08", pivot: Illuminate\Database\Eloquent\Relations\Pivot {#4077 risk_id: 3, riskstatus_id: 4, user_id: 2, }, }, ], }

But now I have the problem that I can't store the user_id in a variable to work with. I have tried the following:

App\Risk::find(3)->riskstatus->user_id Exception with message 'Property [user_id] does not exist on this collection instance.'

and

>>> App\Risk::find(3)->riskstatus->pluck('user_id') => Illuminate\Support\Collection {#4093 all: [ null, ], }

and

>>> >>> App\Risk::find(3)->riskstatus->pivot->user_id PHP Parse error: Syntax error, unexpected T_SR on line 1

Can you give me a tip on what I'm doing wrong?

Thanks Matthias

riskmate_mw
OP
5 years ago

Level 1

I have found a solution but I think there is a much easier way isn't it?

>>> $temp = App\Riskstatus::find(4)->risk->pluck('pivot') => Illuminate\Support\Collection {#4122 all: [ Illuminate\Database\Eloquent\Relations\Pivot {#4110 riskstatus_id: 4, risk_id: 3, user_id: 2, }, ], } >>> $temp->pluck('user_id') => Illuminate\Support\Collection {#4114 all: [ 2, ], }

SilenceBringer
5 years ago

Level 55

@riskmate_mw

App\Risk::find(3)->riskstatus

returns you collection instance

Illuminate\Database\Eloquent\Collection {#4096 all: [ App\Riskstatus {#4088 id: 4, name: "Rsik accepted", created_at: "2020-07-24 07:00:08", updated_at: "2020-07-24 07:00:08", pivot: Illuminate\Database\Eloquent\Relations\Pivot {#4077 risk_id: 3, riskstatus_id: 4, user_id: 2, }, }, ], }

in order to get user_id you must select specified record from the collection, first one for example:

App\Risk::find(3)->riskstatus->first()->pivot->user_id

riskmate_mw
OP
5 years ago

Level 1

This makes total sense and works. Thanks for your help.

Please or to participate in this conversation.