Laravel SQL更新行由WHERE IN Group By

Question

I have a little problem here. Instead of running 10 queries inside for loop, I wan't to do it on one query.

My code looks like this at the moment:

foreach($games as $game_id){
    DB::table('game_serials')
    ->whereNull('user_id')
    ->where('game_id', $game_id)
    ->update([
        'user_id' => 1
    ]);
}

So, I wan't to do it on one query instead of running this same for lets say 5000 users, which would be extremely slow to finish...

I have tried to do something like this:

DB::table('game_serials')
->whereNull('user_id')
->whereIn('game_id', $games)
->groupBy('game_id')
->limit(10)
->update([
    'user_id'    => 1
]);

$games array looks like this:

Array
(
    [0] => 1
    [1] => 2
    [2] => 3
    [3] => 4
    [4] => 5
    [5] => 6
    [6] => 7
    [7] => 8
    [8] => 9
    [9] => 10
)

But it only updates 10 rows by first value from array.
How can I make it to update only one row per game_id in array?

Answer 1

I understand what you're trying to achieve and took it upon myself to do this as a challenge. The below code is tested and does what you desire. It fetches the first free serial that isn't assigned to any user for each game in the list. The second query assigns the selected serials to the user.

$serials = DB::table('game_serials')
    ->selectRaw('min(id) as id')
    ->whereNull('user_id')
    ->whereIn('game_id', $games)
    ->groupBy('game_id')
    ->get()
    ->pluck('id');

$result =  DB::table('game_serials')
    ->whereIn('id', $serials)
    ->update(['user_id' => 1]);