Pivot record instance ->save() updates multiple rows

[marijnhurkens]

• Laravel Version: 5.5.13
• PHP Version: 7.1
• Database Driver: SQlite

Description:

I have 2 models: Tickets and Actions which have a n:n relationship. The pivot model (ActionTicket) has a field call order because a ticket can have multiple actions of the same type.

Ticket 1, action 1, order 1
Ticket 1, action 2, order 2,
Ticket 1, action 1, order 3
etc.

The pivot model also has some other metadata. Now at some point I want to update the metadata on the pivot model. This is my code:

$pivotmodel = $ticket->actions()->wherePivot('order', '=', $order)->first()->pivot;
$pivotmodel->metadata = 'bla';
$pivotmodel->save();

With the database rows from the example above I have the following problem. When I execute the code with $order = 3 then the metadata field on rows 1 and 3 are updated. The query generated is as follows:

update "action_ticket" set "metadata" = 'bla', "updated_at" = '---' where "ticket_id" = '1' and "ticket_action_id" = '1'

As you can see the order is gone.

How can I solve this problem?

[marijnhurkens]

Okay never mind I solved it by adding id to the withPivot() function and now the update query is:

update "action_ticket" set "metadata" = 'bla', "updated_at" = '---' where "id"=idOfPivotRecord

[mahdiahmadi73]

@marijnhurkens how do you add id on withPivot functuion ? sample code please . i have same problem but and cant fix it .this is my code . where i add id ?
public function post()
{
return $this->belongsToMany('App\Post','comment_post','comment_id','post_id')
->withPivot('count');
}

[marijnhurkens]

Just add id to the array you can pass to withPivot. Example:

public function actions() {
        return $this->belongsToMany(TicketAction::class)->withPivot([
            'id',
            optional other columns here...
        ])->withTimestamps()->using(TicketTicketAction::class);
    }

[mahdiahmadi73]

@marijnhurkens
not working
i have two model post and comment with manyToMany relation .pivot table is comment_post and pivot column is count , i changed my code to ;

public function comments()
{
return $this->belongsToMany('App\Comment','comment_post','post_id','comment_id')
->withPivot(['id','count']);
}

here is my table :

then i execute this code i expect just row 1 updated and count set to 9 but multiple rows updated :

$post=Post:find(1);
$comment=$post->comments()->wherePivot('count','=',5)->first()->pivot ;
$comment->count=9 ;
$comment->save() ;

and now table :

what should i do ?

[marijnhurkens]

First of all you can use three backticks (`) to insert code blocks on github. This makes the code more readable.

Second I assume you mean this?

$comment->count = 9 ;

Which Laravel version do you use?

[mahdiahmadi73]

yes i mean $comment->count=9 ;
i using laravel 5.4

[marijnhurkens]

Okay, I'm using 5.5, could be this issue.

If you want to stay at 5.4 you can try:

$post = Post:find(1);
$comment = $post->comments()->wherePivot('count','=',5)->first();
$post->comments()->updateExistingPivot($comment->id, ['count' => 9]);
// no save() needed I think

Update:

You can also try to dump the generated sql query or use the laravel-debugbar module: https://github.com/barryvdh/laravel-debugbar

[mahdiahmadi73]

thanks its woked but i think this code consumer more query one for getting comment->id and another for update ,if you find better solution please post here,thank you for help ! @marijnhurkens