Level 1
Does noone has any idea?
May 30, 2016
10
Level 1
update / sync pivot table with multiple primary keys
Hello,
i have a problem to handle pivot table with more than 2 primary keys. I have three tables:
- posts
- users
- post_user
But in the post_user i want additional data, so i have:
- user_id
- post_id
- list_id ( in which list the post was sended )
- status
Normally you combine only the user_id and the post_id. How do i handle the other key? I have try the following:
$post_user = $user->posts()->where('list_id', '=', $list_id)->first();
$post_user->pivot->status = $request->input('status');
$post_user->pivot->save();
don't work (nothing has happened)
$post_user = PostUser::where('user_id', '=', $user_id)->where('post_id, '=', $post_id)->where('list_id', '=', $list_id)->first();
$post_user->status = $request->input('status');
$post_user->save();
don't work (Column not found: 1054 Unknown column 'id' in 'where clause' (SQL: update post_user set status = 1, updated_at = 2016-05-30 13:42:14 where id is null))
Is there a way to work with sync, something like:
$user->posts()->sync(['postid', 'listid'], ['status' => ...]);
i hope someone could help me
Level 54
You could either include the extra fields when defining the relationship (withPivot, see docs excerpt below), or use a model instead of a pivot table.
Docs Excerpt re withPivot:
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:
return $this->belongsToMany('App\Role')->withPivot('column1', 'column2');If you want your pivot table to have automatically maintained created_at and updated_at timestamps, use the withTimestamps method on the relationship definition:
return $this->belongsToMany('App\Role')->withTimestamps();
Level 1
I have an extra field in the relationship and i also have an model, but when i update my relation, i have the following problem:
post_user:
| user_id | post_id | list_id | status |
| --- | --- | --- | --- |
| 1 | 1 | 1 | 1 |
| 2 | 2 | 1 | 1 |
| 1 | 3 | 1 | 1 |
| 1 | 3 | 2 | 0 |
when i want update now my post 3 of user 1, i cant do that with sync, cause my post_id 3 is two times in the table
$user->posts()->sync([3 => ['status' => 1], 3 => ['status' => 0]]);
the other methods with save, i describe top also don't work.
Level 1
sorry that i push this thread, but i need help, i have no idea how i can save my pivot with 2 keys!
Level 88
Take a look at the method updateExistingPivot
$user->roles()->updateExistingPivot($roleId, $attributes);
Documentation: https://laravel.com/docs/5.2/eloquent-relationships#inserting-many-to-many-relationships
However I do think you will still have issues here since you have two posts with the same id connected to the user but they are both in a different list. You probably need an extra where query here
2 likes
Level 1
@bobbybouwmann thank you, i also think that i need an extra where query. Can you tell me how it must looks like? I have a lot of same id's with different lists for a user.
Level 88
To be honest I think you should rethink your database structure, I don't know the specifics of your project, so I can't help you with that or I need more information.
I think you can simply do
$user->roles()->where('something', 'something')->updateExistingPivot($roleId, $attributes);
Note: I didn't tested this, but you can give it a try
1 like
Level 1
@bobbybouwmann thank you, u give the right tip.
$user->roles()->wherePivot('list_id', $list_id)->updateExistingPivot($list_id, ['list_id' => $new_list_id, 'status' => true]);
this work for me
1 like
Level 88
Yeah of course it was wherePivot. Glad you fixed it ;)
1 like
Level 6
Hy ...I have the similar problem.
I have 3 tables:
*pellets *specie (there are the trees) *specie_pellet (this is the pivot table)
The specie_pellet table is a pivot for the composition of a pellet and has an extra field called percentuale (percentage).
Obviously the relation is belongsToMany in Specie Class and belongsToMany in Pellet Class.
*legenda
5 = biopellet
1 = red fir
25 = percentage
5 = biopellet
7 = silver fir
75 = percentage
specie_pellet table pivot have:
pellet_id - specie_id - percentuale
5 - 1 -25
5 - 7 -75
This is the form
@if(count($pellet->specie))
{!! Form::open(['method' => 'PUT', 'url' => ['composizione/pellet/update', $pellet->id]]) !!}
<table class="table table-bordered">
<thead>
<th>Specie</th>
<th>Percentuale</th>
<th>Azione</th>
</thead>
<tbody>
@foreach($pellet->specie as $specie)
<tr>
<td>
{!! Form::select('specie[]', $listaSpecie, $specie->id, ['class' => 'form-control']) !!}
</td>
<td>
<input type="text" class="form-control" name="percentuale[]" value="{{ $specie->pivot->percentuale }}">
</td>
<td><a class="btn btn-danger" href="{{ url('composizione/pellet/elimina/id/' . $pellet->id . '/specie/' . $specie->id) }}">Elimina</a> </td>
</tr>
@endforeach
</tbody>
</table>
{!! Form::submit('Aggiorna Composizione', ['class' => 'btn btn-success']) !!}
{!! Form::close() !!}
@else
<h4>Non è possibile modificare un pellet che non ha nessuna Composizione</h4>
<a class="btn btn-success" href="{{ url('admin/pellets') }}">+ Crea Nuova Composizione</a>
@endif
}
This is the route
Route::put('composizione/pellet/update/{id}', 'PelletController@updateComposizione')->name('composizione.update');
This is the controller
public function updateComposizione(\App\Http\Requests\ComposizioneUpdateRequest $request, $id)
{
$pellet = Pellet::find($id);
$specie = Input::get('specie');
$percentuali = Input::get('percentuale');
$pellet->specie()->sync([$specie => ['percentuale' => $percentuali]]);
return redirect('admin/pellets/')->with('ok_success', 'Composizione aggiornata correttamente');
}
but don't work ...can help me?
Please or to participate in this conversation.