使用Eloquent插入和更新基于外键的多行的最佳方法是什么？

Question

I have a table named 'answers' which will store customer answer about questionnaire. The table looks like this

answers
+-----------------+
|id (PK)          |
|customer_id (FK) |
|question_id (FK) |
|value            |
|_________________|

my request would be like this

{
    "customer_id":1,
    "answer": {
        "1":"My Name", //"1" is question_id , "My Name" is value
        "2":"My Phone Number" //"2" is question_id, "My Phone Number" is value
    }
}

What i'm trying to achieve is doing a single query or single action on Eloquent instead of foreach the answer one by one to check if the question_id and customer_id already exists it will update otherwise it will insert the answer to table 'answers'.

By doing foreach/looping one by one per answer data, it shouldn't be a hard problem. However if i already got 3000++ rows in 'answers' table then it would be a big problem.

What is the best approach to do this? Maybe is there any third-party library that i can use to do Mass Insert/Updates multiple rows based on foreign key check?

Answer 1

You can do this by laravel's UpdateORCreate Method which do the exact thing that you want.

In General the statement is like below:

$model=Model::UpdateOrCreate(["condtion1"=>"value1",...],["field1"=>"value1",...]);

In your case it'll be something like below:

$answer=Answer::UpdateOrCreate(["customer_id"=>$customer_id,"question_id"=>$question_id],["value"=>$value]);

For more information, Check Laravel Eloquent Documentation (Other Creation Methods)