update multi rows in laravel by eloquent-CodePudding

Suppose we have one dataBase that have one table called fathers; and another table called children.

I want get all children whose father is mamali.

$pls = children::where(['father_id' => 5, 'isGoodBoy' => true])->take(4)->get();

And i want change $pls and set father_id to 7,8,50,55. so can do this one by one request in foreach :

for ($i = 0; $i < count($pls); $i  ) {
    $pls[$i] = $arayWhoWantBaby[$i];
    $pls[$i]->save();
}

This work but do with many request...(in this example 1 request for get and 4 request for update ! )

I want do this with one or two DB request,one to get data from DB and another set new data with one request to do all work and update items $pls[0][1][2]...

one thing as "in" keyword in sql for update ;

CodePudding user response：

This is what I meant. Honestly, I'd just stick with the extra 4 queries. Small updates like that shouldn't be an issue.

Also, doing it like this will not trigger any Eloquent events.

$father_ids = [7, 8, 50, 55];
$children_ids = children::where(['father_id' => 5, 'isGoodBoy' => true])->take(4)->pluck('id')->all();

$sql = <<<SQL
UPDATE
    children
SET
    father_id = CASE id
        WHEN :children_id_1 THEN :father_id_1
        WHEN :children_id_2 THEN :father_id_2
        WHEN :children_id_3 THEN :father_id_3
        WHEN :children_id_4 THEN :father_id_4
    END
WHERE
    id IN (:children_id_1, :children_id_2, :children_id_3, :children_id_4)
SQL;

$bindings = [
    'children_id_1' => $children_ids[0],
    'children_id_2' => $children_ids[1],
    'children_id_3' => $children_ids[2],
    'children_id_4' => $children_ids[3],
    'father_id_1' => $father_ids[0],
    'father_id_2' => $father_ids[1],
    'father_id_3' => $father_ids[2],
    'father_id_4' => $father_ids[3],
];

DB::update($sql, $bindings);