How to update a pivot table using Eloquent in laravel 5

Question

I am new to laravel. I am working on a laravel 5 application and I am stuck here. I have two models:

class Message extends Eloquent{ public function user() { return $this->belongsTo('App\User', 'from'); } public function users() { return $this->belongsToMany('App\User')->withPivot('status'); } } class User extends Eloquent { public function messages() { return $this->hasMany('App\Message', 'from'); } public function receive_messages() { return $this->belongsToMany('App\Message')->withPivot('status'); } } 

There is a many-to-many relationship between the post and the user, giving me a pivot table as such:

 Table Name: message_user Colums: message_id user_id status 

I have an SQL query as such:

 update message_user set status = 1 where user_id = 4 and message_id in (select id from messages where message_id = 123) 

How can I translate this query into laravel equivalent?

Answer 1

The code below solved my problem:

 $messages = Message::where('message_id', $id)->get(); foreach($messages as $message) $message->users()->updateExistingPivot($user, array('status' => 1), false); 

Answer 2

You can use one of these two functions, sync() attach() and the difference in two words is that Sync will receive the array as the first argument and synchronize it with the pivot table (delete and add the passed keys to your array), which means if you get 3,2,1 as the value in your connection table and synchronize with the 3,4,2 values, the synchronization will automatically delete the value 1 and add the value 4 for you. where Attach will take one identifier value

GIST: if you want to add additional values ​​to your connection table, pass it as the second argument to sync() as follows:

 $message = Messages::find(123); $user = User::find(4); // using ->attach for single message $user->message()->attach($message->id,['status' => 1 ]); // using ->sync for multiple messages $message2 = Messages::find(456); // for testing $user->message()->sync([$message->id => ['status' => 1 ],$message2->id => ['status' => 1 ] ]); 

Answer 3

Laravel 5.8

First, enable the search withPivot method with your own belongsToMany

Copied from my own code to save time

 // I have 3 columns in my Pivot table which I use in a many-to-many and one-to-many-through scenarios $task = $user->goalobjectives()->where(['goal_objective_id'=>$goal_objective_id,'goal_obj_add_id'=>$goal_obj_add_id])->first(); //get the first record $task->pivot->goal_objective_id = $new; //change your col to a new value $task->pivot->save(); //save 

The caveat is that your pivot table must have the primary key 'id' .

If you do not want this, you can try the following:

 $tasks=$user->posts()->where(['posts_id'=>$posts_id,'expires'=>true])->get()->pluck('id'); // get a collection of your pivot table data tied to this user $key=join(",",array_keys($tasks->toArray(),$valueYouWantToRemove)); $tasks->splice($key,1,$newValueYouWantToInsert); $c = array_fill(0,$tasks->count(),['expires'=>true]); //make an array containing your pivot data $newArray=$tasks->combine($c) //combine the 2 arrays as keys and values $user->posts()->sync($newArray); //your pivot table now contains only the values you want 

Update July 4th Update to the above snippet.

 //Ideally, you should do a check see if this user is new //and if he already has data saved in the junction table //or are we working with a brand new user $count = $user->goalobjectives->where('pivot.goal_obj_add_id',$request->record)->count(); //if true, we retrieve all the ids in the junction table //where the additional pivot column matches that which we want to update if($count) { $ids = $user->goalobjectives->where('pivot.goal_obj_add_id',$request->record)->pluck('id'); //convert to array $exists = $ids->toArray(); //if user exists and both saved and input data are exactly the same //there is no need //to update and we redirect user back if(array_sum($inputArray) == array_sum($exists)) { //redirect user back } //else we update junction table with a private function //called 'attachToUser' $res = $this->attachToUser($user, $inputArray, $ids, $request->record); }//end if elseif(!$count) { //we are working with a new user //we build an array. The third pivot column must have equal rows as //user input array $fill = array_fill(0,count($inputArray),['goal_obj_add_id'=>$request->record]); //combine third pivot column with user input $new = array_combine($inputArray,$fill); //junction table updated with 'user_id','goal_objective_id','goal_obj_add_id' $res = $user->goalobjectives()->attach($new); //redirect user if success } //our private function which takes care of updating the pivot table private function attachToUser(User $user, $userData, $storedData, $record) { //find the saved data which must not be deleted using intersect method $intersect = $storedData->intersect($userData); if($intersect->count()) { //we reject any data from the user input that already exists in the database $extra = collect($userData)->reject(function($value,$key)use($intersect){ return in_array($value,$intersect->toArray()); }); //merge the old and new data $merge = $intersect->merge($extra); //same as above we build a new input array $recArray = array_fill(0,$merge->count(),['goal_obj_add_id'=>$record]); //same as above, combine them and form a new array $new = $merge->combine($recArray); //our new array now contains old data that was originally saved //so we must remove old data linked to this user // and the pivot record to prevent duplicates $storedArray = $storedData->toArray(); $user->goalobjectives()->wherePivot('goal_obj_add_id',$record)->detach($storedArray); //this will save the new array without detaching //other data previously saved by this user $res = $user->goalobjectives()->wherePivot('goal_obj_add_id',$record)->syncWithoutDetaching($new); }//end if //we are not working with a new user //but input array is totally different from saved data //meaning its new data elseif(!$intersect->count()) { $recArray = array_fill(0,count($userData),['goal_obj_add_id'=>$record]); $new = $storedData->combine($recArray); $res = $user->goalobjectives()->wherePivot('goal_obj_add_id',$record)->syncWithoutDetaching($new); } //none of the above we return false return !!$res; }//end attachToUser function 

This will work for a pivot table that does not have a basic auto-increment identifier. without an auto-increment identifier, the user cannot update, insert, delete any row in the pivot table by accessing it directly.

Answer 4

You can use the updateExistingPivot method to update the pivot table.