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構文」を利用する場合は、ユニークキーの設定が必要です。
1 2 3 4 5 6 7 8 9 10 11 |
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するという処理です。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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する場合は以下のようになります。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
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です。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
$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レコードずつの処理ではなく、大量のデータをまとめて処理する方式です。)
1 2 3 4 5 6 7 8 9 10 |
// カラムの項目名を取り出す $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()
1 |
->epilog('ON DUPLICATE KEY UPDATE `title`=VALUES(`title`), `body`=VALUES(`body`), `modified`=now()') |
CakePHP 3.4以降では記述方法が変更に
また、CakePHP 3.4以降は下記のように推奨の記述方法が変更されています。
1 2 3 |
$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の関連記事
CakePHP4のCSS、JavaScript、画像のブラウザへのキャッシュをコントロールするCakePHP3でレコードを保存(追加、更新、Insert、Update)する複数の方法を紹介
CakePHP3でモデルなしフォームからCSVをアップロードしレコードを更新する方法解説
CakePHP3でPHP Simple HTML DOM Parserを使ってスクレイピングする方法
CakePHP3のInsert On Duplicate Key Update(upsert)構文を解説・バルク処理も
CakePHP3の1対多での連携を中間テーブルを使った多対多の連携に変更するときの手順
CakePHP3でデフォルトのソート条件を設定してユーザの選択肢たソート条件を有効にする方法
CakePHP3で Ajaxを使う方法の解説。3.6以降対応。Successとthenの両方を解説。
CakePHP3でパンくずの指定は HTMLヘルパーを使って指定する方法を解説
CakePHP3にOGPをfetch、asignを利用してテンプレートごとに指定する方法を解説
その他の「CakePHP3」に関する記事一覧
GoogleAdwords
GoogleAdwords
この記事が参考になったと思いましたらソーシャルメディアで共有していただけると嬉しいです!
関連記事
-
国際化と地域化の翻訳機能「__()」を使って定数に変数を埋め込む方法
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など会員制のサイトを簡単に実現可能。その導入方法、カスタマイズ方法を解説。
-
CakePHP3でパンくずの指定は HTMLヘルパーを使って指定する方法を解説
CakePHP3でパンくずの指定方法の解説。2つのヘルパーがあるが簡単なHTMLヘルパーを使った方法を、実際の状況に合わせて3つのパターン(エレメント化、ブロック化)にして解説。
-
cakephp3 カスタムバリデーションを簡易的に実装する方法
CakePHP3の独自のバリデーションをテーブルクラス内に簡単に記述する方法を解説。他のテーブルクラスでは使えないが、記述する量は少なく実装できるため、他で使わない処理を書くのには便利。