How to update multiple records value in database using laravel?

Question

Initially, students table looks like below.

id | invite_count
-----------------
10 | 5
12 | 0
15 | 1
25 | 0

I am using the below code to update the invite count for students.

Student::whereIn("id", $this->request->studentIds)
        ->update([
            'invite_count' => DB::raw('invite_count+1')
        ]);

But according to my requirement, I can have the same id more than one time in the $this->request->studentIds array like [10, 15, 12, 10, 25, 12].

At the movement, the output looks like below.

id | invite_count
-----------------
10 | 6
12 | 1
15 | 2
25 | 1

But I want output like below.

id | invite_count
-----------------
10 | 7
12 | 2
15 | 2
25 | 1

How can I archive?

John Lobo · Accepted Answer · 2021-12-15 07:50:18Z

2

One way is using chunk query using chunkById.To work update make sure to invite_count column fillable in model

$ids=[10, 15, 12, 10, 25, 12];

$groupByIds=array_count_values($ids);


Student::whereIn("id",array_keys($groupByIds))
        ->chunkById(50, function ($students)use($groupByIds) {
            $students->each(function ($student, $key)use($groupByIds) {
                $student->update(['invite_count' => $student->invite_count+($groupByIds[ $student->id])]);
            });

        }, $column = 'id');

or

 Student::whereIn("id",array_keys($groupByIds))
            ->chunkById(50, function ($students)use($groupByIds) {
                $students->each(function ($student, $key)use($groupByIds) {
                    $student->invite_count=$student->invite_count+$groupByIds[ $student->id]);
$student->save();
                });

        }, $column = 'id');

Ref:https://laravel.com/docs/8.x/eloquent#chunking-results

edited Dec 15, 2021 at 7:50

answered Dec 15, 2021 at 3:03

John Lobo

15.4k2 gold badges18 silver badges25 bronze badges

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

7 Comments

Senthurkumaran Over a year ago

Thanks @John for your answer. But here you are using each loop with an update then I think it will do update separately for each?

John Lobo Over a year ago

@Senthur yes separate loop but with query chunk so it won't retrieve all records once . If it's possible without a loop then it's good but not sure in your use case it can be done since increment is purely based on a number of ids count so. Let's wait if someone comes with a better solution so we both learn from it

Senthurkumaran Over a year ago

Thanks I upvoted your answer. It's actually a good solution. But I will wait as you mentioned answer let's see.

Senthurkumaran Over a year ago

above code isn't updating the value in the db.

Senthurkumaran Over a year ago

you are correct. the update also works if we add in the fillable. Anyway, both will work. Thanks again. I don't think any better answer will come. so I approved.

|

Collectives™ on Stack Overflow

How to update multiple records value in database using laravel?

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related