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

Question

我是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');
    }
}

消息和用户之间存在多对多的关系，因此我得到了以下如此命名的数据透视表：

Table Name: message_user
Colums:
message_id
user_id
status

我有一个如下的SQL查询：

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

我该如何将这个查询转换成 Laravel 的等价语句？

- Fokwa Best

---

兄弟，你说的“from”是什么意思？ - Mohamed Mo Kawsara

外键 @Mohamed - Fokwa Best

伙计，第二个参数是关联表的名称，在你的例子中应该是 'message_user'，在 User 模型中，messages 的完整关系如下： return $this->hasMany('App\Message\,'message_user','user_id','message_id'); - Mohamed Mo Kawsara

伙计，我刚刚检查了一下，我很确定，在你发给我的同一个链接中，请搜索以下这行代码：return $this->belongsToMany('App\Role', 'user_roles', 'user_id', 'role_id'); - Mohamed Mo Kawsara

感谢你的输入，伙计。用户可以创建一个或多个消息。一条消息可以被一个或多个用户接收。一个用户可以接收零个或多个消息。 - Fokwa Best

Answer 1

下面的代码解决了我的问题：

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

- Fokwa Best

---

我可以知道false是什么意思吗？ - Muhammad Dyas Yaskur

确定是否要更新 updated_at 列的时间戳。 - 4unkur

Answer 2

您可以使用这两个函数之一，sync()和attach()的区别简而言之是Sync将数组作为其第一个参数，并将其与中间表同步（删除和添加传递给它的键），这意味着如果在连接表中有3,2,1这三个值，并且传递给Sync值为3,4,2，则Sync将自动为您删除值1并添加值4。而Attach将接收单个ID值。

要点：如果您想将额外的值添加到连接表中，请将其作为第二个参数传递给sync()，如下所示：

$message = Messages::find(123);
$user = User::find(4);

// using attach() for single message
$user->message()->attach($message->id, [
    'status' => 1
]);



$message2 = Messages::find(456); // for testing

// using sync() for multiple messages
$user->message()->sync([
    $message->id => [
        'status' => 1
    ],
    $message2->id => [
        'status' => 1
    ],
]);

- Mohamed Mo Kawsara

---

感谢你的输入，@Mohamed。同步对我没有起作用。 - Fokwa Best

基于您上面的评论，@FokwaBest先生，您只需要多对多关系，因此您的Eloquent关系存在误导。如果您愿意，我可以更新我的答案并编写正确的关系，然后sync()就能正常工作 :) - Mohamed Mo Kawsara

老兄，实际上当用户创建一条消息时，它会被存储在消息表中。交叉表只会记录消息 ID 和接收者（$user_id）的信息。现在它运作得相当不错，但我仍然很想知道你的处理方法。 - Fokwa Best

感谢@MohamedKawsara！我想提醒大家，在sync()方法调用中最重要的是false标志。因为如果没有传递它，所有与当前$message->id不同的其他记录都将被分离。此外，请注意，当我们附加时，方法调用是attach($id, ['attr1' => 'val1'])，但在sync()调用中，我们有sync([$id **=>** ['attr1' => 'val1']]); - М.Б.

Answer 3

这里有一个简单的示例，展示如何更新数据透视表列。

    $query = Classes::query();
    $query = $query->with('trainees')
                   ->where('user_id', Auth::id())
                   ->find($input['classId']);

    foreach ($query->trainees as $trainee) {
       $trainee->pivot->status = 1 //your column;
       $trainee->pivot->save();
    }

注意：确保您的关系数据必须在数组中。 希望这能对您有所帮助 :) 编码愉快。

- Parth kharecha

Answer 4

Laravel 5.8

首先，通过在belongsToMany后链接withPivot方法，允许您的枢纽列可搜索。

为了节省时间，这是从我的代码中复制过来的。

// 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

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

如果您不想要这个主键，可以尝试以下方法：

$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

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

如果透视表没有主键自增id，以下方法可以使用。没有自增id，用户无法直接访问透视表来更新、插入或删除任何行。

- Bruce Tong

Answer 5

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

- Babar Sajjad

---

谢谢@Babar。你能否添加一些示例代码，以便我可以使用该方法来查询帖子？ - Fokwa Best

您可以在http://laravel.com/docs/5.0/eloquent#working-with-pivot-tables查看文档。实现此操作的参考代码为User::find(1)->roles()->updateExistingPivot($roleId, $attributes); - Babar Sajjad