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

2020/01/25

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;

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'));
    }

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.'));
        }
    }

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);
}

$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();

// カラムの項目名を取り出す

$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()')

1	->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);

$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を使う
　
　

- CakePHP 3.x 4.x 5.x CakePHP3

最後までお読みいただきましてありがとうございます。
この記事が参考になったと思いましたらソーシャルメディアで共有していただけると嬉しいです！

Message コメントをキャンセル

※入力いただいたコメントは管理者の承認後に掲載されます。

: CakePHP3でアソシエーション先のカラムでデータをソートして取得したい

アソシエーション先のテーブルのカラムをキーにソートをしたデータを取得したい！ときの記述方法を解説。「orderAsc()」ではなく「contain()」の中にSort条件を記述する。

: CakePHP4のCSS、JavaScript、画像のブラウザへのキャッシュをコントロールする

CakePHP4、CakePHP3でブラウザにキャッシュさせる設定の解説。CSS、JavaScript、画像をブラウザにキャッシュさせるのか、定期的にリロードする設定にするのかの設定が可能。

: CakePHP4、CakePHP5系の認証処理でログインの有無の確認、ユーザ情報の取得の方法

CakePHP4系、5系のログイン認証の可否のチェック、および、ログインユーザの情報を取得する方法のまとめ。複数の方法があるが手続きが微妙に異なるためまとめてみた。

: CakePHP3のCakeDC/UsersのUserHelperでログアウトやreCAPTCHAをカスタマイズ

CakeDC謹製Usersプラグインの紹介。UserHelperを利用し、ログアウトのリンクや権限があるときのみ表示されるリンク、プロフィールページへのリンク、reCAPTCHAの設置方法などを解説。

: CakePHP3の1対多での連携を中間テーブルを使った多対多の連携に変更するときの手順

CakePHP3で「1対多」の連携を中間テーブルを利用した「多対多」の連携に変更するときの手順のまとめ。中間テーブルの設定やModelの変更などを間違いやすい箇所を指摘しながらの解説。

: CakePHP4、5でSELECT telephone as tel FROM usersの様にasでカラムに別名を付ける方法

CakePHP4、CakePHP5でクリエビルダーを利用してテーブルからレコードを取得する際、asを使用してカラムの別名で値を取得したい場合の対応方法を解説。また、別名にはハイフンやアスタリスクなど使用できない文字がある点も解説。

: CakePHP3でComposerでインストールできないプラグイン、外部ライブラリを vendorに入れて手動で読み込む方法

CakePHP3にComposerからインストールできないプラグインやライブラリなどを利用する方法、vendorにファイルを設置し、composer.jsonを更新し、それを呼び出す方法を詳細に解説。

: cakephp3 カスタムバリデーションを簡易的に実装する方法

CakePHP3の独自のバリデーションをテーブルクラス内に簡単に記述する方法を解説。他のテーブルクラスでは使えないが、記述する量は少なく実装できるため、他で使わない処理を書くのには便利。

: CakePHPで同一テーブル内の値を比較する条件でレコードを取得する方法

CakePHPの同一テーブルにある項目の値を比較し条件に合致するレコードを取得する方法を解説。［”項目名”=>”値”]ではなく[”項目名 = 項目名”］と書くところがポイント。

: CakePHP4系、CakePHP5系のexists()でカラムを指定して値の有無をチェックする方法解説

CakePHP4、5で指定したカラムに特定の値に該当のレコードの有無をチェックするにはexists()を使う。単純にカラムを指定する方法から複数条件をand、orで探すこともできる。