Update data array in Laravel

Question

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 class="input-group">
    <select name="product_id[]" class="js-basic-single form-select" 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 |

Do you realize you update always the same field (product_id for example) for a given id ? — Vincent Decaux
– Vincent Decaux, Commented Jun 2, 2022 at 21:44
If I send two values in the array, it takes the last one and updates the fields with it, that happens with the quantity field and measure_id. — Edward Palen
– Edward Palen, Commented Jun 2, 2022 at 22:29

Donkarnash · Accepted Answer · 2022-06-03 15:02:41Z

1

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

edited Jun 3, 2022 at 15:02

answered Jun 2, 2022 at 23:27

Donkarnash

12.9k5 gold badges30 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Edward Palen Over a year ago

Hello, thank you very much for answering. Very good explanation and I thank you, but 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. What I am looking for is to update the related data in (quote_detail), in the first option that you show, it would not be the appropriate one, because there is no relationship between (quotes and products). In the second option, using the upsert method, it duplicates the records, which I understand should create them if they don't exist.

Donkarnash Over a year ago

@EdwardPalen Do you mean the relation between Quote(table:quotes) and QuoteDetail(table:quote_details) is like one-to-many and have defined eloquent relationship? Quote and QuoteDetail are two models

Edward Palen Over a year ago

Only in quote_details I have a belongsTo relationship with Quote and Products

Donkarnash Over a year ago

@EdwardPalen Have updated answer as per clarification in your comment. With upsert method if you are using MySQL as database then the primary key is required in the first argument as it does not consider the second argument. Probably that is the reason why you get duplicate records.

Edward Palen Over a year ago

Using the updateOrInsert methods as you recommend, it doesn't update the data, it inserts the changes as new records.

|

Collectives™ on Stack Overflow

Update data array in Laravel

1 Answer 1

Edit:

Eloquent way

Query Builder way

Update - Better Approach

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Edit:

Eloquent way

Query Builder way

Update - Better Approach

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related