CODEWITHSUNDEEP
phpmysqllaravel
Michel Setyawan Mulyono
Michel Setyawan Mulyono

Reputation: 85

Laravel Update Multiple Records with Different Values for Each Record

I want to update multiple records with different values for each record. What should I do with the laravel eloquent? Here's my code example. Thanks in advance.

    $employee_presences = Employee_presence::
    where('employee_id', $report_history->employee_id)
    ->whereBetween('presence_date',[$report_history->report->start_period,$report_history->report->end_period])
    ->get();

    foreach ($presences_data as $presence_data) 
    {
        foreach ($employee_presences as $employee_presence)
        {
            $updated_presences = Employee_presence::
            where('id', $employee_presence->id)->update([
                'presence_value' => $presence_data['presence_value']
            ]);
        }
    }

These are the values inside $presences_data. This is the new data for updating the records.

enter image description here

These are the records $employee_presences:

enter image description here

Upvotes: 1

Views: 10039

Answers (4)

Helios Live
Helios Live

Reputation: 153

One way to do it is to use upserts https://laravel.com/docs/9.x/eloquent#upserts

Flight::upsert([
    ['presence_value' => '...', 'presence_date' => '...', 'employee_id' => 99],
    ['presence_value' => '...', 'presence_date' => '...', 'employee_id' => 150]
], ['employee_id', 'presence_date'], ['presence_value']);

Upvotes: 4

Healyhatman
Healyhatman

Reputation: 1659

I'm not entirely sure about MySQL, but this works for SQL Server.

First, build a collection of the data you want to update - column_name => $value, including the ID

Then create a "values list" from it, like this

$value_items = $update_array->map(function ($entry) {
    if (!isset($entry)) {
        return 'NULL';
    }
    if (is_iterable($entry)) {
        return collect($entry)
            // Wrap strings in quotes
            ->map(fn($item) => isset($item)
                ? (is_string($item) ? "'$item'" : $item)
                : 'NULL'
            )
            ->join(',');
    }
    if (is_string($entry)) {
        return "'$entry'";
    }
    return $entry;
})
->map(fn($rows) => '(' . $rows . ')')
->join(',');

Which will give you a string along the lines of (1, 'foo', 'bar'), (2, 'foo', 'bar')

Turn that values list into a raw subquery

$sub_query = "SELECT * FROM (VALUES $value_items) AS sub_query (id, column_one, column_two, etc)";

Perform the update query

YourModel::query()
  ->joinSub($sub_query, 'values_list_alias', 'values_list_alias.id', 'your_model.id')
  ->update([
    'column_one' => DB::raw('values_list_alias.column_one'),
    'column_two' => DB::raw('values_list_alias.column_two')
  ]);

Upvotes: 0

S. Scholte
S. Scholte

Reputation: 119

It is possible by using the mysql CASE statement. AFAIK there isn't a build in function in eloquent or the query builder for case statements, so you would have to do that part of the query with raw statements.

I don't really understand which value you want for which record in your example (may be my bad), but i will try my best to help you with a function you can use in general:

suppose you have a selection of employee id's from employees you want to update, and for every employee id you also know the presence value you want to give them. You could make an array $data where those id's are the key and the presence value the value. then you can throw that array in the following function:

public static function updatePresencePerEmployee($data){
    //make the complex case statement first
    $case = "(case";
    foreach ($data as $employee_id => $presence_value ){
        $case.=" when employee_id = ".$employee_id." then ".$presence_value;
    }
    $case.=" end)";

    DB::table('employee_presence')
        //shift the keys of the array which are employee id's to values so laravel can use it in the whereIn clause
        ->whereIn('employee_id', array_keys($data))
        //dont forget to throw the case string in to the DB:raw function
        ->update(['presence_value' =>DB::raw($case)]);
}

I use this function in one of my applications itself but it needs improvement. For instance right now you need to be sure those values in the case statement arent an sql injection.

If you don't understand what happens, reading this thread would help. it tells you how you can use the case statement.

MySQL - UPDATE multiple rows with different values in one query

Upvotes: 2

Professor
Professor

Reputation: 908

There's no way of updating multiple records with different data each on the same query. You can bulk update your records by running one query per group of changes:

Employee_presence::whereIn('employee_id', <list of ids>)->update(
    [
        'presence_value' => 1,
        'presence_date' => "2021-07-12"
    ]
);

Employee_presence::whereIn('employee_id', <different ids>)->update(
    [
        'presence_value' => 0.5,
        'presence_date' => "2021-07-13"
    ]
);

If there are no groups (meaning every single record will have a different value), you would need to update them separately in different queries. Not very efficient.

Upvotes: 2

Related Questions