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

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

: CakePHP3で保存前にバリデーション結果を取得する2つの方法

CakePHP3でデータベースに値を保存する前にバリデーションを行い、その結果によって処理を振り分ける方法について解説。「$topic->errors()」と「$topic->hasErrors()」の2つの方法がある。

: CakePHP3で生の SQLの実行はConnectionManagerを使う

CakePHP3で生の SQL文を実行する方法を解説。クリエビルダーを使う場合は TableRegistryを利用するが、SQLを実行する場合は ConnectionManagerを使う。プリペアードステートメントの使用方法も解説。

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

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

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

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

: CakePHP 2.3で確認画面付きのお問い合わせフォームの作り方

CakePHPで確認画面付きのお問い合わせフォーム、メールフォームの作り方をサンプルを提示しながら解説。

: CakePHP3のインストールでURL rewriting……のエラーが！その解決方法解説

CakePHP3をインストールしたら URL rewriting is not properly configured.のエラーが。原因は.htaccessが有効になっていないこと。httpd.confに設定を追加すればOK。

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

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

: CakePHP3のForm Helperの使い方のまとめ

CakePHP3になりフォームヘルパーの使い方も大きく変わりましたので、使い方をまとめました。基本的な使い方からプラスアルファの便利な使い方まで紹介。

: CakePHP3でkeywords、DescriptionをHTMLヘルパーを使って設定する

CakePHP3のkeywordsとdescriptionを設定する方法の解説。CakePHP3にはmetaタグを編集するHTMLヘルパーが用意されているためそれを利用すればOK！ポイントはブロック化を有効にすること。