有没有更好的方法来在Laravel中更新多个记录？

huangapple
go评论89阅读模式
英文:

Is there a better way to update as multiple records in Laravel?

问题

我对这个主题进行了一些研究，但最后我发现我的方法对于更新约100个记录 x 10个字段的列表是可接受的。是否有更好的方法？

这些数组来自一个类似于以下形式的表单：

  1. <div class="input-group">									
  2.     <div class="col-1">
  3.        <input class="input" type="text" name="id[]" id="id[]" value="...">
  4.     </div>	
  5.     <div class="col-1">
  6.        <input class="input" type="text" name="field1[]" id="field1[]" value="...">
  7.     </div>	
  8.     <div class="col-1">
  9.        <input class="input" type="text" name="field2[]" id="field2[]" value="...">
  10.     </div>	
  11.     <div class="col-1">
  12.        <input class="input" type="text" name="field3[]" id="field3[]" value="..">
  13.     </div>	
  14.     ...
  15. </div>

为了保存数据库表中的所有更改，我在控制器中执行以下PHP代码：

  1. // 批量更新数据库 | PUT
  2. public function save($table, $fields='field1,field2,field3...')
  3. {
  5.     // 替换SQL语句
  6.     $sqlReplace0 = "UPDATE ".$table." SET XXX_listFields WHERE id = ";
  8.     // 将字段列表转换为数组
  9.     $arrayFields = explode(',', $fields);
  11.     // 循环记录
  12.     $sql = '';
  13.     $i = 0;
  14.     foreach($_POST['id'] as $id) {
  16.         // 循环字段
  17.         $fields = '';
  18.         foreach($arrayFields as $field) {
  19.             if ($fields == '') {
  20.                 $fields .= $field." = '".$_POST[$field][$i]."'";
  21.             } else {
  22.                 $fields .= ",".$field." = '".$_POST[$field][$i]."'";
  23.             }
  24.         }
  26.         // 替换SQL中的字段和值
  27.         $sql .= str_replace('XXX_listFields', $fields, $sqlReplace0)."'".$id."';\n";
  29.         $i++;
  30.     }
  32.     // 更新所有记录
  33.     $result = DB::unprepared($sql); 
  35.     ...
  36. }

在我的情况下，这个方法运行得相当快...但我想知道是否有更好的方法...

英文:

I did some research on the subject but at the end I found that my approach was acceptable for updating about a list of 100 records x 10 fields. Is there a better way to do that?

The arrays come from a form looking like this:

  1.  &lt;div class=&quot;input-group&quot;&gt;									
  2.     &lt;div class=&quot;col-1&quot;&gt;
  3.        &lt;input class=&quot;input&quot; type=&quot;text&quot; name=&quot;id[]&quot; id=&quot;id[]&quot; value=&quot;...&quot;&gt;
  4.     &lt;/div&gt;	
  5.     &lt;div class=&quot;col-1&quot;&gt;
  6.        &lt;input class=&quot;input&quot; type=&quot;text&quot; name=&quot;field1[]&quot; id=&quot;field1[]&quot; value=&quot;...&quot;&gt;
  7.     &lt;/div&gt;	
  8.     &lt;div class=&quot;col-1&quot;&gt;
  9.        &lt;input class=&quot;input&quot; type=&quot;text&quot; name=&quot;field2[]&quot; id=&quot;field2[]&quot; value=&quot;...&quot;&gt;
  10.     &lt;/div&gt;	
  11.     &lt;div class=&quot;col-1&quot;&gt;
  12.        &lt;input class=&quot;input&quot; type=&quot;text&quot; name=&quot;field3[]&quot; id=&quot;field3[]&quot; value=&quot;..&quot;&gt;
  13.     &lt;/div&gt;	
  14.     ...
  15.  &lt;/div&gt;

In order to save all the changes in the db table, I execute the following PHP in the controller:

  1.  //  update db in bulk | PUT
  2.  public function save($table,$fields=&#39;field1,field2,field3...&#39;)
  3.  {
  5.     // replace sql statement
  6.     $sqlReplace0 = &quot;UPDATE &quot;.$table.&quot; SET XXX_listFields WHERE id = &quot;;
  8.     // fields list into array
  9.     $arrayFields = explode(&#39;,&#39;,$fields);
  11.     // loop records
  12.     $sql = &#39;&#39;;
  13.     $i = 0;
  14.     foreach($_POST[&#39;id&#39;] as $id) {
  16.        // loop fields
  17.        $fields = &#39;&#39;;
  18.        foreach($arrayFields as $field) {
  19.           if ($fields == &#39;&#39;) {
  20.              $fields .= $field.&quot; = &#39;&quot;.$_POST[$field][$i].&quot;&#39;&quot;;
  21.           } else {
  22.              $fields .= &quot;,&quot;.$field.&quot; = &#39;&quot;.$_POST[$field][$i].&quot;&#39;&quot;;
  23.           }
  24.        }
  26.        // replace fields &amp; values in sql
  27.        $sql .= str_replace(&#39;XXX_listFields&#39;,$fields,$sqlReplace0).&quot;&#39;&quot;.$id.&quot;&#39;;\n&quot;;
  29.        $i++;
  31.     }
  33.     // update all records
  34.     $result = DB::unprepared($sql); 
  36.     ...
  38.  }

It works pretty fast in my case... but I would like to learn if there is a better way...

答案1

得分: 1

为了回答你的问题，你可以使用 Laravel 的 upsert 功能。

以下是一个 upsert 的示例：

  1. YourModelName::upsert([
  2.     ['firstfield' => 'request var', 'secondfield' => 'request var', 'thirdfield' => 'request var'],
  3.     ['firstfield' => 'request var', 'secondfield' => 'request var', 'thirdfield' => 'request var']
  4. ], ['firstfield']);

upsert 用作更新或创建方法，如果数据与数据库中的记录匹配（在这种情况下我们使用 firstfield），则记录将被更新，如果没有匹配，则将其插入为新行。

你还可以通过循环将变量存储在集合中，然后将其传递给 upsert 方法。

以下是一个示例：

  1. $data = [];
  3. for ($i = 0; $i < count($request->id); $i++){
  4.     $data[] = [
  5.         'id'     => $request->input('id')[$i],
  6.         'field1' => $request->input('field1')[$i],
  7.         'field2' => $request->input('field2')[$i],
  8.         'field3' => $request->input('field3')[$i]
  9.     ];
  10. }
  12. YourModelName::upsert($data, ['id']);

希望这有所帮助。

英文:

To answer you question, you can use the upsert feature of Laravel.

here's an example of upsert:

  1. YourModelName::upsert([
  2.     [&#39;firstfield&#39; =&gt; &#39;request var&#39;, &#39;secondfield&#39; =&gt; &#39;request var&#39;, &#39;thirdfield&#39; =&gt; &#39;request var&#39;],
  3.     [&#39;firstfield&#39; =&gt; &#39;request var&#39;, &#39;secondfield&#39; =&gt; &#39;request var&#39;, &#39;thirdfield&#39; =&gt; &#39;request var&#39;]
  4. ],  [&#39;firstfield&#39;]);

upsert serves as an update or create method, if the data matched with a record in your database (which in this case we use firstfield) than the record will be updated, and if not, then it will be inserted as a new row.

you can also run your variables through a loop, and store them in a collection and then pass it to the upsert method.

here's an example:

  1. $data = [];
  3. for ($i = 0; $i &lt; count($request-&gt;id); $i++){
  4.     $data[] = [
  5.         &#39;id&#39;     =&gt; $request-&gt;input(&#39;id&#39;)[$i],
  6.         &#39;field1&#39; =&gt; $request-&gt;input(&#39;field1&#39;)[$i],
  7.         &#39;field2&#39; =&gt; $request-&gt;input(&#39;field2&#39;)[$i],
  8.         &#39;field3&#39; =&gt; $request-&gt;input(&#39;field3&#39;)[$i]
  9.     ];
  10. }
  12. YourModelName::upsert($data, [&#39;id&#39;]);

hope it helps.

huangapple
  • 本文由 发表于 2023年5月28日 19:57:16
  • 转载请务必保留本文链接：https://go.coder-hub.com/76351365.html
  • bulkupdate
  • laravel-8
  • php
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定