MySQL 如何在 Laravel 5 中使用 Eloquent 更新数据透视表

How to update a pivot table using Eloquent in laravel 5

提问by Fokwa Best

I am new to laravel. I am working on a laravel 5 app and I am stuck here. I have 2 models as such:

我是laravel的新手。我正在开发一个 Laravel 5 应用程序，但我被困在这里。我有两个模型：

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 exist a many-to-many relationship between Message and User giving me a pivot table as such:

Message 和 User 之间存在多对多关系，为我提供了一个数据透视表：

Table Name: message_user
Colums:
message_id
user_id
status

I have an SQL query as such:

我有一个这样的 SQL 查询：

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 to the laravel equivalent?

如何将此查询转换为 Laravel 等效项？

回答by Fokwa Best

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);

回答by Mohamed Kawsara

You may use one of these two functions, sync()attach()and the difference in a nutshell is that Sync will get array as its first argument and sync it with pivot table (remove and add the passed keys in your array)which means if you got 3,2,1 as valued within your junction table, and passed sync with values of, 3,4,2, sync automatically will remove value 1 and add the value 4 for you. where Attach will take single ID value

您可以使用这两个函数之一，sync()attach()简而言之，Sync 将获取数组作为其第一个参数并将其与数据透视表同步（删除并在数组中添加传递的键），这意味着如果您有 3,2 ,1 作为您的联结表中的值，并使用值 3,4,2 传递同步，同步将自动删除值 1 并为您添加值 4。其中 Attach 将采用单个 ID 值

The GIST:if you want to add extra values to your junction table, pass it as the second argument to sync()like so:

GIST：如果您想向联结表添加额外的值，请将其作为第二个参数传递给sync()像这样：

$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 ] ]);

回答by Babar Sajjad

For Updating your pivot table you can use updateExistingPivot method.

要更新数据透视表，您可以使用 updateExistingPivot 方法。

回答by Bruce Tong

Laravel 5.8

Laravel 5.8

First, allow your pivot columns to be searchable by chaining the withPivotmethod to your belongsToMany

首先，通过将withPivot方法链接到您的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 needs to have a primary 'id'key.

需要注意的是，您的数据透视表需要有一个主'id'键。

If you don't want that then 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

4th July UpdateUpdate to above snippet.

7 月 4 日更新更新上述代码段。

//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 pivot table which doesn't have a primary auto increment id. without a auto increment id, user cannot update,insert,delete any row in the pivot table by accessing it directly.

这适用于没有主要自动增量 ID 的数据透视表。如果没有自动增量 ID，用户将无法通过直接访问来更新、插入、删除数据透视表中的任何行。