How to use Eloquent 'saveMany' with pivot table?

Question

In Laravel I have a table of Users and a table of Notices, in a many-to-many relationship. Each notice is given to a set of users, and each user can mark the notice as read independently. The intermediate table (notice_user_map) has a 'read' column for this.

I'm trying to create "mark all as read" functionality, but can't see how to do this through Eloquent. Here's the base code:

$notices = $user->notices()
    ->where('read', 0)
    ->get();

foreach ($notices as $notice) {
    $notice->pivot->read = 1;
}

$authUser->notices()->saveMany($notices);

So this finds all unread notices for that user and loops through them, setting the 'read' attribute on the intermediate (pivot) table. But the last line gives the error

Argument 1 passed to Illuminate\Database\Eloquent\Relations\BelongsToMany::saveMany() must be of the type array, object given

If I create an array manually, I get this error:

Integrity constraint violation: 1062 Duplicate entry '4293672207-2904708423' for key 'notice_user' (SQL: insert into notice_user_map (created_at, notice_id, updated_at, user_id) values (2016-01-06 17:53:39, 4293672207, 2016-01-06 17:53:39, 2904708423))

I can't access the pivot like $authUser->notices()->pivot either.

What is the correct way to mass-save data in the pivot table?

Thomas Kim Thomas Kim · Accepted Answer · 2016-01-06T18:33:18

You can just update it rather than retrieving the values and then looping through them. Try something like this:

$user->notices()->wherePivot('read', 0)->update(['read' => 1]);

This should ultimately defer to the query builder and update the pivot table in a single query rather than looping through and creating numerous queries.

Edit: If your pivot table also has timestamps, then this will throw an error because Eloquent does not prefix these column names (not sure why). In that case, you can still achieve this with the query builder:

DB::table('notice_user_map')
    ->where('user_id', $user->id)
    ->where('read', 0)
    ->update(['read' => 1]);

How to use Eloquent 'saveMany' with pivot table?

1 Answers