php Question posted in
Official manual can be found here.

How can I update multiple rows in a Yii2 Postgres table?

$campaigns array of data:

[
...
27 => array:10 [
    "name" => "0ca9b377dd066958_ru_acc9_map86"
    "cid" => "53099327454745088"
    "status" => 2
    "id" => 36842
  ]
...
]

Prepare before update:

foreach ($campaigns as $campaign) {
            $id = $campaign['campaign_id'];
            $name = $campaign['name'];
            $status = $campaign['status'];
            $index = Campaign::getIndex($name);
            $aSource = Campaign::aSource($name, $status);
            $streamId = Campaign::getStreamIdByName($name);

            $updateValues[] = [
                'id' => $id,
                'name' => $name,
                'status' => $status,
                'index' => $index,
                'a_source' => $aSource,
                'stream_id' => $streamId,
                'updated_at' => new Expression('NOW()')
            ];
        }

How can i do update all rows with id in ids from $campaigns array with new values of name, status, index, a_source, stream_id and updated_at?

Help me please, i have tried all options from doc and chatGPT, like:

–batch insert

a) with `ON DUPLICATE KEY UPDATE` 
b) and `ON CONFLICT (id) DO UPDATE SET`
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I tried this way for now, and i will be grateful if you share your experience:

    $transaction = Yii::$app->db->beginTransaction();

        try {

            foreach ($campaigns as $campaign) {
                $cid = $campaign['id'];
                $name = $campaign['name'];
                $status = $campaign['status'];
                $index = Campaign::getIndex($name);
                $aSource = Campaign::aSource($name, $status);
                $streamId = Campaign::getStreamIdByName($name);

                // Update the row in the database
                Yii::$app->db->createCommand()->update(
                    'campaigns',
                    [
                        'name' => $name,
                        'status' => $status,
                        'index' => $index,
                        'a_source' => $aSource,
                        'stream_id' => $streamId,
                        'updated_at' => new Expression('NOW()')
                    ],
                    ['cid' => $cid]
                )->execute();
            }

            $transaction->commit();

        } catch (Exception $e) {
            $transaction->rollBack();
        }

  2. 0

    If number of rows of campaings is so huge, better fetching data in batches. It will give your memory/hardware resource usage under control.

    $query = (new Query)->from('campaigns');
foreach ($query->batch() as $campaigns) {
    // $rows is an array of 100 (default) or fewer rows from campaigns table
$transaction = Yii::$app->db->beginTransaction();

        try {

            foreach ($campaigns as $campaign) {
             ......
             }    

            $transaction->commit();

        } catch (Exception $e) {
            $transaction->rollBack();
        }
}

    https://www.yiiframework.com/doc/api/2.0/yii-db-query#batch()-detail

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search