CakePHP3のInsert On Duplicate Key Update（upsert）構文を解説・バルク処理も

CakePHP3でInsert On Duplicate Key Update（upsert）を処理

　
CakePHP3はフレームワークですので、データベースへのアクセスが簡単にできるようになっています。
　
ですが、「Insert … On Duplicate Key Update構文」については、Cookbookを探してもわかりにくかったので、改めてその解説をしようと思います。
　
　

Insert … On Duplicate Key Update構文とは

　
「Insert … On Duplicate Key Update構文」は、Updateと Insertをつなげて「Upsert」とも呼ばれている機能で、保存するレコードがすでにあれば Updateし、なければ Insertするという便利な機能です。
詳しくは下記の記事などが参考になります。
Insert On Duplicate Key Update構文の使い方
　
MySQLオフィシャル
https://dev.mysql.com/doc/refman/5.6/ja/insert-on-duplicate.html
　
　

CakePHP3の標準の更新（Update）処理について

サンプルとなる Topicsテーブル

　
今回は、下記の Topicsテーブルを想定します。
普通は想定しませんが、同一日に同一カテゴリーの topics（お知らせ）は配信しないということで、「category_id」「topics_date」の 2つで「topics_unique」というユニークキーを設定しています。
このキーと重複するレコードは Updateし、重複しない場合は Insertするという処理です。
※「Insert … On Duplicate Key Update構文」を利用する場合は、ユニークキーの設定が必要です。
　

CREATE TABLE `topics` (
 `id`                   int(11) unsigned  NOT NULL AUTO_INCREMENT,
 `category_id`          smallint(6)       NOT NULL,
 `topics_date`          date              NOT NULL,
 `title`                text              NOT NULL,
 `body`                 text              NOT NULL,
 `created`              datetime          NOT NULL,
 `modified`             datetime          NOT NULL,
 PRIMARY KEY (`id`)
 UNIQUE KEY `topics_unique` (`category_id`,`topics_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CakePHP3で Bakeしたときの処理内容

　
CakePHP3で Bakeすると、アクション「edit」は下記のようになります。
これは、「id」をキーとしてレコードを取得し、入力された値で Updateするという処理です。
　

    public function edit($id = null)
    {
        $topic = $this->Topics->get($id, [
            'contain' => []
        ]);
        if ($this->request->is(['patch', 'post', 'put'])) {
            $topic = $this->Topics->patchEntity($topic, $this->request->getData());
            if ($this->Topics->save($topic)) {
                $this->Flash->success(__('The topic has been saved.'));

                return $this->redirect(['action' => 'index']);
            }
            $this->Flash->error(__('The master maker could not be saved. Please, try again.'));
        }
        $this->set(compact('topic'));
    }

CakePHP3で「Insert … On Duplicate Key Update構文」を処理する場合

　
これを、入力された「category_id」「topics_date」が一致した場合に Updateする場合は以下のようになります。
　

    public function editUpsert()
    {
        if ($this->request->is(['patch', 'post', 'put'])) {
            // INSERT ON DUPLICATE KEY UPDATE の処理
            $getData = $this->request->getData();
            $topicFirst = $this->Topics->find()->where(["category_id"=>$getData["category_id"],"topics_date"=>$getData["topics_date"]])->first();
            if($topicFirst){
                $topic = $topicFirst;
            }else{
                $topic = $this->Topics->newEntity();
            }
            $saveData = $this->Topics->patchEntity($topic,$getData);
            if ($this->Topics->save($saveData)) {
                $this->Flash->success(__('The topic has been saved.'));

                return $this->redirect(['action' => 'index']);
            }
            $this->Flash->error(__('The master maker could not be saved. Please, try again.'));
        }
    }

「Insert … On Duplicate Key Update構文」を foreach()で処理する

　
「$this->Topics->find()->where()->first()」の処理で「category_id」「topics_date」が一致するレコード取得を試みて、レコードが取得できたか出来なかったかで処理を分けています。
　
上記のような処理を書くことはほとんどないと思いますが、csvファイルなどを取り込む処理などで複数レコードがある場合は、上記を参考に取得したレコードを foreach()でぐるぐる回す処理にすれば OKです。
　

$dataCsvs = [
    [ "category_id" => "1",
      "topics_date" => "2019-12-30",
      "title"       => "年末年始",
      "body"        => "年末年始のお知らせ", ],
    [ "category_id" => "2",
      "topics_date" => "2020-01-01",
      "title"       => "新年",
      "body"        => "新年のご挨拶", ],
        ];

// INSERT ON DUPLICATE KEY UPDATE の処理
foreach($dataCsvs as $data){
    $topicFirst = $this->Topics->find()->where(["category_id"=>$data["category_id"],"topics_date"=>$data["topics_date"]])->first();
    if($topicFirst){
        $topic = $topicFirst;
    }else{
        $topic = $this->Topics->newEntity();
    }
    $data = $this->Topics->patchEntity($topic,$data);
    $this->Topics->save($data);
}

「Insert … On Duplicate Key Update構文」をバルク処理する方法

　
上記の方法でも処理は可能なのですが、1レコードごとに Selectしてレコードの有無を確認して、その上で Updateか Insertをしますのでレコード数が多いときは処理時間が問題になってきます。
　
そんなときのためにバルク処理も用意されています。
（「バルク処理」は、1レコードずつの処理ではなく、大量のデータをまとめて処理する方式です。）
　
　

// カラムの項目名を取り出す
$columns = array_keys($dataCsvs[0]);

$query = $this->Topics->query();
$query->insert($columns);
$valuesExpression = $query->clause('values')->setValues($dataCsvs);

$query->values($valuesExpression)
      ->epilog('ON DUPLICATE KEY UPDATE `title`=VALUES(`title`), `body`=VALUES(`body`)')
      ->execute();

　
　
ポイントとしては「epilog()」の部分ですね。
ここに記述するのは、ユニークキーになる項目ではなく、更新する項目を記述する項目という点です。
　
今回は、「$query->insert($columns);」の処理で「$columns」で配列全体を指定しましたが、「$query->insert(["title","body"]);」のように、登録するカラム名を直接記述する方法でも問題ありません。
　
　

「modified」を更新したい場合は更新項目に追加する

　
前項の処理を実行した場合、「modified」は更新されません。
　
先に紹介した「foreach()」の処理では CakePHP3で処理するため、レコード更新時には自動的に「modified」も更新されます。
ですが、バルク処理をした場合は前項の記述内容では「modified」は更新されないため、「modified」も更新したい場合はその内容も記述する必要があります。
　
記述する内容は、「epilog()」の行のみですが、下記の通り「, `modified`=now()」を追記します。
　

->epilog('ON DUPLICATE KEY UPDATE `title`=VALUES(`title`), `body`=VALUES(`body`), `modified`=now()')

CakePHP 3.4以降では記述方法が変更に

　
また、CakePHP 3.4以降は下記のように推奨の記述方法が変更されています。
　

$query->clause('values')->values($dataCsvs);
//                           ↓変更
$query->clause('values')->setValues($dataCsvs);

Insert … On Duplicate Key Update構文に関連する記事

　
CakePHP2の頃の話や、ここで紹介した「Insert … On Duplicate Key Update構文」の方法が分からずに直接 SQLを実行する方法で紹介した記事などを以下に書いています。
　
Insert On Duplicate Key Update構文の使い方
CakePHP 2.3でOn Duplicate Key構文を実装
CakePHP3で生の SQLの実行はConnectionManagerを使う
　
　

CakePHP3の関連記事