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 CakePHP3

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

Message コメントをキャンセル

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

: 国際化と地域化の翻訳機能「__()」を使って定数に変数を埋め込む方法

CakePHP4で定数に変数を埋め込み、翻訳機能「__()」で変数に値を入れる方法を紹介。定型の文章の一部だけを置換したい場合に利用すると便利。

: CakePHP3でDocumentRootやwebroot、imgフォルダのURLやドメイン、パスを取得

URLやドメイン、フォルダへのパスの取得は、ビューではUrlHelperを使い、コントローラーではRouterクラスを使います。第2引数の指定でURLを取得することも可能。

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

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

: CakePHP 2.3　連携先のテーブルの項目で条件抽出する場合

アソシエーション（連携）している先のテーブルの項目で条件抽出する際の考え方と注意点をサンプルソースを用いて説明しています。

: CakePHP 2.3　ログイン、操作履歴、アクセスログ出力

CakePHPでログインや操作履歴などのアクセスログ出力処理を作成します。

: CakePHP3の検索プラグイン「friendsofcake/search」の様々な検索の仕方の実装方法

CakePHP3で検索をするプラグイン「friendsofcake/search」の検索条件のカスタマイズ方法の解説。検索項目を増やしたり、以上、以下での検索や、チェックボックスによる検索の方法などを解説。

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

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

: CakePHP4のユーザ管理・ログイン認証プラグインCakeDC/Usersのインストール解説

CakePHP4のユーザ管理プラグイン Usersは、ユーザ登録、メール認証、ログイン認証、ユーザ管理、権限管理、reCAPTCHAなど会員制のサイトを簡単に実現可能。その導入方法、カスタマイズ方法を解説。