How to update field value in pivot table w/o sync

I have a pretty standard many-to-many relationship between my User and OrientationClass models (user_orientation_class is the join table), and I have an extra field on the pivot table called attended which is a boolean field, e.g..

    public function orientationClasses() {
        return $this->belongsToMany(OrientationClass::class, 'user_orientation_class')
            ->withPivot(['attended']);
    }

As part of my process, I want to mark all that have attended the class by setting attended = true. My front end form has a list of the people in the class, with a checkbox for each user. In my controller, is there a method I can use to update all of the users at once by marking the attended field as true? In looking at the docs, it looks like you can do that if you want to sync the users (e.g. syncWithPivotValues(), sync(), etc), but I don't want to add/remove users, just mark the attended field for selected users.

In the docs, I see updateExistingPivot(), with this example:

$user->roles()->updateExistingPivot($roleId, [
    'active' => false,
]);

which in my case would be something like

$oclass->users()->updateExistingPivot($userId, [
    'attended' => true,
]);

since I am passing in the $oclass model, but that doesn't make sense, because I want to update all the users in the class, and this just updates the first record.

Is there a way to bulk update the attended field for all users in a particular class?

LaryAI

2 years ago

Level 58

Yes, you can bulk update the attended field for all users in a particular class by using the updateExistingPivot method with a closure. Here's an example of how you can achieve this:

$oclass->users()->updateExistingPivot($userId, function ($pivot) {
    $pivot->attended = true;
});

In this example, $oclass is the instance of the OrientationClass model, and $userId is the ID of the user you want to update. However, since you want to update all users in the class, you can omit the $userId parameter.

$oclass->users()->updateExistingPivot(null, function ($pivot) {
    $pivot->attended = true;
});

This will update the attended field to true for all users associated with the $oclass model.

Note that the closure passed to updateExistingPivot receives the pivot model as an argument, allowing you to modify its properties. In this case, we're setting the attended property to true.

Make sure to replace users() with the appropriate method name that defines the relationship between the OrientationClass and User models in your code.

wonder95

2 years ago

Level 5

Unfortunately when I try the suggested code, I get an error

`Illuminate\Database\Eloquent\Relations\BelongsToMany::updateExistingPivot(): Argument #2 ($attributes) must be of type array, Closure given,

which makes sense, since this is the function signature for updateExistingPivot()

    /**
     * Update an existing pivot record on the table.
     *
     * @param  mixed  $id
     * @param  array  $attributes
     * @param  bool  $touch
     * @return int
     */
    public function updateExistingPivot($id, array $attributes, $touch = true)
    {

so passing in a closure won't work.

kokoshneta

2 years ago

Best Answer

Level 27

The updateExistingPivot() method is meant to update values for the specific user model you’re passing through as the first argument.

If you want to update all rows in the pivot table that match the class model, you’ll have to do it manually, I think:

$relation = $oclass->users();

DB::table($relation->getTable())
	->where($relation->getForeignPivotKeyName(), $oclass->getKey())
	->update([
		'attended' => true
	])
;

That should do it, I think, but you should probably add a ->dd() before the update call, just to make sure I didn’t mix up parent/foreign/related pivot keys in the ->where() call.

1 like

wonder95

2 years ago

Level 5

@kokoshneta Yup, I was thinking the same thing.

The only thing your code was missing was the whereIn() o specify the list of those who actually attended. It ended up looking like this

 DB::table($relation->getTable())
            ->where($relation->getForeignPivotKeyName(), $oclass->getKey())
            ->whereIn($relation->getRelatedPivotKeyName(), $attended)
            ->update([
                'attended' => true
            ]);

Thanks.

newbie360

2 years ago

Level 24

@wonder95

\DB::enableQueryLog();
	$oclass->users()->newPivotQuery()->update(['attended' => true]);
dd(\DB::getQueryLog());

Please or to participate in this conversation.