Laravel原生更新查询中如何为CASE语句设置数据绑定批量更新多行

Hey there! I see you're trying to add proper parameter binding to your CASE statement for bulk updating products in Laravel, and those previous attempts didn't work out. Let's break down why those approaches failed, then jump into the correct way to do this.

Why Your Previous Attempts Failed #

• First two approaches: When you use setBindings(), it replaces all existing bindings (including the ones from your whereIn('id', [1,2,3]) clause). That's why you got the Invalid parameter number error—Laravel was expecting 3 parameters for the WHERE IN but got 6 instead, or vice versa depending on the order.
• Third approach: The DB::raw() method returns an Illuminate\Database\Query\Expression object, which doesn't have a setBindings() method. That's why you hit the "undefined method" error.

Correct Implementation #

The key here is to pass your CASE statement bindings separately without overriding the existing ones from whereIn. Here's a clean, maintainable way to do it:

// Define your ID-to-stock mappings
$stockUpdates = [
    1 => 1000,
    2 => 1001,
    3 => 1003,
];

// Build the CASE statement parts and collect bindings
$caseSegments = [];
$bindings = [];

foreach ($stockUpdates as $productId => $newStock) {
    $caseSegments[] = 'WHEN ? THEN ?';
    $bindings[] = $productId;
    $bindings[] = $newStock;
}

// Combine the segments into a full CASE statement
$caseStatement = implode(' ', $caseSegments);

// Perform the update—pass bindings as the second argument to update()
Product::whereIn('id', array_keys($stockUpdates))
    ->update(
        [
            'stock' => DB::raw("CASE id {$caseStatement} END")
        ],
        $bindings
    );

How This Works #

1. We first define our desired updates in an array for clarity and maintainability.
2. We loop through each update to build the individual WHEN ? THEN ? segments of the CASE statement, and collect all the parameter values into a bindings array.
3. We pass the bindings array as the second argument to update(). Laravel automatically merges these bindings with the ones from the whereIn clause, so there's no conflict or parameter number mismatch.
4. This approach keeps your code safe from SQL injection (thanks to proper binding) and works seamlessly with Laravel's ORM.

Alternative: Using Named Bindings (For Extra Clarity) #

If you prefer named bindings for readability, you can do this instead:

$stockUpdates = [
    1 => 1000,
    2 => 1001,
    3 => 1003,
];

$caseSegments = [];
$bindings = [];

foreach ($stockUpdates as $productId => $newStock) {
    $caseSegments[] = "WHEN :id_{$productId} THEN :stock_{$productId}";
    $bindings["id_{$productId}"] = $productId;
    $bindings["stock_{$productId}"] = $newStock;
}

$caseStatement = implode(' ', $caseSegments);

Product::whereIn('id', array_keys($stockUpdates))
    ->update(
        [
            'stock' => DB::raw("CASE id {$caseStatement} END")
        ],
        $bindings
    );

This achieves the same result but uses named parameters, which can make your code easier to debug if you have a lot of updates.

内容的提问来源于stack exchange，提问作者kaan_atakan