CODEWITHSUNDEEP
phplaravellaravel-4eloquent
papas-source
papas-source

Reputation: 1281

Eloquent model mass update

Please correct me if I am wrong, but I think there is no such thing as mass update in an Eloquent model.

Is there a way to make a mass update on the DB table without issuing a query for every row?

For example, is there a static method, something like 

User::updateWhere(
    array('age', '<', '18'),
    array(
        'under_18' => 1 
        [, ...]
    )
);

(yes, it is a silly example but you get the picture...)

Why isn't there such a feature implemented? Am I the only one who would be very happy if something like this comes up?

I (the developers), wouldn't like to implement it like:

DB::table('users')->where('age', '<', '18')->update(array('under_18' => 1));

because as the project grows, we may require the programmers to change the table name in the future and they cannot search and replace for the table name!

Is there such a static method to perform this operation? And if there is not, can we extend the Illuminate\Database\Eloquent\Model class to accomplish such a thing?

Upvotes: 79

Views: 198395

Answers (9)

suryadeep bhujel
suryadeep bhujel

Reputation: 77

Laravel 6.*

We can update mass data on query as follow :

Appointment::where('request_id' , $appointment_request->id)
    ->where('user_id', Auth::user()->id)
    ->where('status', '!=', 'Canceled')
    ->where('id', '!=', $appointment->id)
    ->update([
        'status' => 'Canceled',
        'canceled_by' => Auth::user()->id
    ]);

Upvotes: 3

Pierre
Pierre

Reputation: 675

From Laravel 8 you can also use upsert which helped me updated multiple rows at once with each rows having different values.

https://laravel.com/docs/8.x/eloquent#upserts

Upvotes: 0

CodeToLife
CodeToLife

Reputation: 4171

Another example of working code of the mass query and mass update in same instruction:

Coordinate::whereIn('id',$someCoordIdsArray)->where('status','<>',Order::$ROUTE_OPTIMIZED)
       ->update(['status'=>Order::$ROUTE_OPTIMIZED]);

Upvotes: 0

catalin87
catalin87

Reputation: 623

A litle correction to @metamaker answer:

DB::beginTransaction();
     // do all your updates here
        foreach ($users as $user) {
            $new_value = rand(1,10) // use your own criteria
            DB::table('users')
               ->where('id', '=', $user->id)
               ->update(['status' => $new_value  // update your field(s) here
                ]);
        }
    // when done commit
DB::commit();

Now you can have 1 milion different updates in one DB transaction

Upvotes: 18

Irfandi D. Vendy
Irfandi D. Vendy

Reputation: 1004

If you need to update all data without any condition, try below code

Model::query()->update(['column1' => 0, 'column2' => 'New']);

Upvotes: 24

Moshood Sikiru
Moshood Sikiru

Reputation: 13

laravel 5.8 you can accomplish mass update like so:

User::where('id', 24)->update (dataAssociativeArray) ;

Upvotes: -4

dmytroy
dmytroy

Reputation: 2397

Use database transactions to update multiple entities in a bulk. Transaction will be committed when your update function finished, or rolled back if exception occurred somewhere in between.

https://laravel.com/docs/5.4/database#database-transactions

For example, this is how I regenerate materialized path slugs (https://communities.bmc.com/docs/DOC-9902) for articles in a single bulk update:

public function regenerateDescendantsSlugs(Model $parent, $old_parent_slug)
    {
        $children = $parent->where('full_slug', 'like', "%/$old_parent_slug/%")->get();

        \DB::transaction(function () use ($children, $parent, $old_parent_slug) {
            /** @var Model $child */
            foreach ($children as $child) {
                $new_full_slug  = $this->regenerateSlug($parent, $child);
                $new_full_title = $this->regenerateTitle($parent, $child);

                \DB::table($parent->getTable())
                    ->where('full_slug', '=', $child->full_slug)
                    ->update([
                        'full_slug' => $new_full_slug,
                        'full_title' => $new_full_title,
                    ]);
            }
        });
    }

Upvotes: 5

bryceadams
bryceadams

Reputation: 2352

Perhaps this was not possible a few years ago but in recent versions of Laravel you can definitely do:

User::where('age', '<', 18)->update(['under_18' => 1]);

Worth noting that you need the where method before calling update.

Upvotes: 139

ek9
ek9

Reputation: 3442

For mass update/insert features, it was requested but Taylor Otwell (Laravel author) suggest that users should use Query Builder instead. https://github.com/laravel/framework/issues/1295

Your models should generally extend Illuminate\Database\Eloquent\Model. Then you access the entity iself, for example if you have this:

<?php
Use Illuminate\Database\Eloquent\Model;

class User extends Model {

    // table name defaults to "users" anyway, so this definition is only for
    // demonstration on how you can set a custom one
    protected $table = 'users';
    // ... code omited ...

Update #2

You have to resort to query builder. To cover table naming issue, you could get it dynamically via getTable() method. The only limitation of this is that you need your user class initialized before you can use this function. Your query would be as follows:

$userTable = (new User())->getTable();
DB::table($userTable)->where('age', '<', 18)->update(array('under_18' => 1));

This way your table name is controller in User model (as shown in the example above).

Update #1

Other way to do this (not efficient in your situation) would be:

$users = User::where('age', '<', 18)->get();
foreach ($users as $user) {
    $user->field = value;
    $user->save();
}

This way the table name is kept in users class and your developers don't have to worry about it.

Upvotes: 73

Related Questions