Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

mpmurph's avatar

How should I bulk update a three-way pivot table?

Hello all -

I'm looking for some advice on the best way to bulk update a three-way pivot table.

Say you have a pivot with the fields: user_id, opinion_id, team_id.

Then, the user has entered a bunch of opinions (love, like, dislike, etc.) about different teams and I have that information in an array of arrays such as:

$opinions = [
    ["user_id" => 1, "opinion_id" => 2, "team_id" => 4],
    ["user_id" => 1, "opinion_id" => 3, "team_id" => 2],
    ["user_id" => 1, "opinion_id" => 2, "team_id" => 7]
];

Is there anyway I can update the pivot model, OpinionTeamUser with one database query?

On creation of the user's opinions, I can batch create with insert() - that's all well and good. The problem arises when I am looking to update... Ideally, I would update a record if it exists, delete a record if the user has deleted their opinion on a given team, and create a new record if they entered an opinion for a team that they had previously not had an opinion on. With delete, I may just need to compare their old opinions with the new ones - but for the update/create aspect, I guess I am looking for an equivalent to the updateOrCreate method, except on a larger scale... I could use that on an individual basis but it would lead to way too many queries.

I have been Googling around and playing with various ideas but have yet to come up with an efficient solution using Eloquent... Any advice would be greatly appreciated!

0 likes
2 replies
toltech's avatar

A thought - have you actually tried running an updateOrCreate on each of the items needed to see if it becomes too slow? Sometimes running them through one by one is "good enough," and the bonus is that you can still use all the goodies bundled in with eloquent statements.

The other thought I have is why not use the relational updating built into eloquent? If you already have a user or team or opinion Model object instance wherever this code is running and its set up with the correct relationships on that given model you should be able to push that data in via the relationship. i.e. $user->team = "team1" (or whatever).

1 like

Please or to participate in this conversation.