Update data array in Laravel-CodePudding

I am trying to update data in a table, sent as an array from a form.

But it happens in the update method in the controller, it receives the array but it only takes the last value of the array and updates it in the table.

I submit my form data like this with a select droopdown:

<div >
    <select name="product_id[]"  data-width="100%">
        @foreach($products as $product)
            <option value="{{ $product->id }}" @if($product->id == $value->product_id) selected @endif>{{ $product->name }}</option>
        @endforeach
    </select>
</div>

If it is from the product_id field:

array:2 [▼ 
  0 => "2" 
  1 => "1" 
]

I get it to update in my controller like this:

for ($i=0; $i<count($request->quantity); $i  ) {
    DB::table('quote_detail')
        ->where('quote_id', $id)
        ->update([
            'product_id'    => $request->product_id[$i],
            'quantity'      => $request->quantity[$i],
            'measure_id'    => $request->measure_id[$i]
    ]);
}

But it only inserts and repeats 1 as the only value in the database.

id | quote_id | product_id | 
 1 |        2 |          1 | 
 2 |        2 |          1 |

The correct thing would be like this:

id | quote_id | product_id | 
 1 |        2 |          2 |  
 2 |        2 |          1 |

CodePudding user response：

Take a moment and understand what you are doing here.

Say the incoming data from request is like

['product_id' => [2,1], 'quantity' => [2,5], 'measure_id' => [3,4]];

The for loop will run 2 times (count($request->quantity)). The values written to database for record identified by quote_id = $id will be:

When loop runs for 1st time: product_id: 2 quantity:2 measure_id: 3
When loop runs for 2nd time: product_id: 1 quantity:5 measure_id: 4 ( values are overwritten for the same record)

So finally the values after last iteration will remain persisted.

Another is that you are trying to identify a record for updation by non-primary key - there can be multiple records in quote_detail table with the same value in quote_id field.

Edit:

I have the table (quotes), in it I store the primary data and in the table (quote_detail), the detail and it is related to the first.

Eloquent way

//Quote.php - Quote (eloquent) model class - table:quotes

public function quoteDetails()
{
    return $this->hasMany(QuoteDetail::class);
}

//QuoteDetail.php - QuoteDetail (eloquent) model class - table: quote_details

public function belongsTo()
{
    return $this->belongsTo(Quote::class);
}

In controller

$quote = Quote::findOrFail($id);
$count = 0;
for($i = 0; $i < count($request->quantity); $i  ) {
    $quote->quoteDetails()
        ->updateOrInsert(
            /**
             * - Check for existing records with following column => value pairs
             * - Combination of quote_id & product_id will act as composite key to identify unique records in quote_details table
             * - Even better to have unique index using composite key quote_id   product_id
             */
            ['quote_id' => $quote->id, 'product_id' => $request->product_id[$i]],

            /** If a record exists it will be updated with following */
            ['quantity' => $request->quantity[$i], 'measure_id' => $request->measure_id[$i]]
        );

// If a matching record (quote_id   product_id) is not found then a new record will be inserted combining both arguments to the `updateOrInsert` method.
}

Laravel Docs - Queries - Update Statements

Query Builder way

for($i = 0; $i < count($request->product_id); $i   ) {
    Db::table('quote_details')
        ->updateOrInsert(
            /**
             * - Check for existing records with following column => value pairs
             * - Combination of quote_id & product_id will act as composite key to identify unique records in quote_details table
             * - Even better to have unique index using composite key quote_id   product_id
             */
            ['quote_id' => $id, 'product_id' => $request->product_id[$i]],

            /** If a record exists it will be updated with following */
            ['quantity' => $request->quantity[$i], 'measure_id' => $request->measure_id[$i]]
        );

// If a matching record (quote_id   product_id) is not found then a new record will be inserted combining both arguments to the `updateOrInsert` method.
}

Update - Better Approach

Better approach would be to define many-to-many relationship between Quote and Product models via quote_details table as pivot table.

//Quote.php
public function products()
{
    $this->belongsToMany(Product::class, 'quote_details');
}

//Product.php
public function quotes()
{
    return $this->belongsToMany(Quote::class, 'quote_details');
}

Then in controller

$quote = Quote::findOrFail($id);

$syncData = [];

for($i = 0; $i < count($request->product_id); $i  ) {
    $syncData[$request->product_id[$i]] = [
        'quantity' => $request->quantity[$i],
        'measure_id' => $request->measure_id[$i]
    ];
}

$quote->products()->sync($data);

The sync() method will only keep the records in quote_details for which the product_id(s) are received via current request.

Say the earlier values in table were:

id=1, quote_id=1, product_id=1, quantity=2, measure_id=6.

and the new values are quote_id=1, product_id=3, quantity=2, measure_id=6

then the older record will be deleted and new record will be created as

id=2(or whatever as per autoincrement), quote_id=1, product_id=3, quantity=2, measure_id=6