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 コメントをキャンセル

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

: CakePHP 2.3　Search Pluginで検索処理　その５入力項目に複数項目入力した場合の AND検索、OR検索

CakePHPの検索プラグイン Search Pluginの検索処理の中で複数項目を入力した場合の AND検索、OR検索についての解説です。

: CakePHP4の定数定義ファイルを環境変数によって本番と開発を振り分ける方法解説

CakePHP4で開発環境と本番環境とで違う設定ファイルを読み込ませて環境ごとに定数を切り替える方法を解説。Apacheのhttpd.confに環境変数を設定しそれを読み込み判別する。

: URL短縮サービス「TTTオンライン（https://ttt.onl）」公開

URL短縮サービス「TTTオンライン（https://ttt.onl）」を公開。メールやSNSでは使いにくい長いURLを短いURLに変換するサービス。QRコードも。Google URL Shortenerが2019年3月にサービス終了。

: CakePHP3でWarning Error: SplFileInfo::openFile()エラーが発生した場合の対処方法

CakePHP3のキャッシュファイルのパーミッションエラー Error: SplFileInfo::openFile()が発生した場合の対応方法解説。app.phpにキャッシュファイルのパーミッション設定を行い、既存のファイルは削除。

: CakePHP3で他のテーブルのマスタテーブルからセレクトボックス（プルダウンリスト）を作る

他のテーブルのマスタのレコードからプルダウンリストを作成し、選択できるようにするサンプルプログラムと解説。ORMの設定によりデータベースの値を取得し、配列を作成し optionsに与える。

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

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

: CakePHP4でロギングスコープやログレベルを使用してログを出し分ける方法を解説

CakePHPのログ出力方法の解説。app.phpにログ設定をし、スコープやレベルを指定してログ出力を振り分ける方法、CakePHP4で配列出力にはvar_exportが必要なことなどを解説。

: CakePHP3ログファイルへの出力・$this->log()、独自ログへの出力方法の解説

コントロール、モデルの変数の中身を見るときはログに出力する方法が有効です。$this->log()を利用すると変数だけじゃなく、連想配列、オブジェクトも簡単にログ出力ができます。

: CakePHP4の規約外のカラムをキーにアソシエーション（テーブル連結）する方法

CakePHPで規定外のカラム名のキーを指定してアソシエーション（テーブル連結）をする方法を解説。アソシエーション名によってはミスが発生しやすい点もあるので注意も必要。

: VirtualBoxにCakePHP3を設置。必要なCentOS、Apache、PHP、MySQL、Composerをインストールし設定する

VirtualBoxにCentOS、Apache、MySQL、PHPをインストールするところから初めてCakePHP3の開発環境を構築する手順を詳細解説。この記事1つで全ての設定が完了する。