如何在CakePHP中批量更新多条带不同条件的数据？

Hey there! I totally get where you're coming from—running 5 separate UPDATE queries (or more as your dataset grows) isn't the most efficient way to handle this. Let's look at a couple of better approaches to do this in fewer database round-trips.

1. Single UPDATE Query with CASE Statements (Most Efficient) #

This method hits the database only once, making it ideal for larger datasets. We'll use a CASE clause to apply different data values based on each order_id.

Raw SQL Equivalent #

First, here's what the combined, optimized SQL would look like:

UPDATE orderdetails 
SET data = CASE 
    WHEN order_id = 1 THEN '{$json1}'
    WHEN order_id = 2 THEN '{$json2}'
    WHEN order_id = 3 THEN '{$json3}'
    WHEN order_id = 4 THEN '{$json4}'
    WHEN order_id = 5 THEN '{$json5}'
END
WHERE order_id IN (1,2,3,4,5);

CakePHP Implementation (Safe & Clean) #

Use CakePHP's query builder to construct this safely (avoids SQL injection risks):

use Cake\ORM\TableRegistry;

$orderDetailsTable = TableRegistry::getTableLocator()->get('Orderdetails');

// Prepare your update data: key = order_id, value = JSON string
$updateMap = [
    1 => $json1,
    2 => $json2,
    3 => $json3,
    4 => $json4,
    5 => $json5
];

// Build the CASE expression
$caseExpression = $orderDetailsTable->query()->newExpr()->addCase(
    // Generate conditions for each order_id
    array_map(function($id) use ($orderDetailsTable) {
        return $orderDetailsTable->query()->newExpr()->eq('order_id', $id);
    }, array_keys($updateMap)),
    // Corresponding JSON values
    array_values($updateMap),
    // Specify data type for the values
    ['string']
);

// Execute the batch update
$orderDetailsTable->query()
    ->update()
    ->set(['data' => $caseExpression])
    ->where(['order_id IN' => array_keys($updateMap)])
    ->execute();

2. Using saveMany (For ORM Workflows) #

If you need to leverage CakePHP's ORM features like validation, model callbacks, or entity logic, saveMany is a cleaner option—though it still runs one query per entity (so less efficient for large datasets):

$orderDetailsTable = TableRegistry::getTableLocator()->get('Orderdetails');

// Fetch all target records
$entities = $orderDetailsTable->find()
    ->where(['order_id IN' => array_keys($updateMap)])
    ->all();

// Patch each entity with its new data
foreach ($entities as $entity) {
    $entity->data = $updateMap[$entity->order_id];
}

// Save all entities in one go
$orderDetailsTable->saveMany($entities);

Key Takeaways #

• Go with the CASE statement approach if raw performance is your top priority—it cuts down on database round-trips drastically.
• Use saveOnly when you need the ORM's built-in features and the number of records is small enough that multiple queries won't impact performance.

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