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
この記事が参考になったと思いましたらソーシャルメディアで共有していただけると嬉しいです!
関連記事
-
-
CakePHP3でWarning Error: SplFileInfo::openFile()エラーが発生した場合の対処方法
CakePHP3のキャッシュファイルのパーミッションエラー Error: SplFileInfo::openFile()が発生した場合の対応方法解説。app.phpにキャッシュファイルのパーミッション設定を行い、既存のファイルは削除。
-
-
CakePHP 2.3 Search Pluginで検索処理 その3入力エリア一つで複数の項目を同時に検索する方法
CakePHPの検索プラグイン Search Pluginの検索処理の中で入力エリア一つで複数の項目を同時に検索する方法を解説。
-
-
CakePHP3でQRコードを作成、表示するライブラリ「cakePHP-QR-Code-Helper」
CakePHPでQRコードを生成するライブラリ「cakePHP-QR-Code-Helper」の紹介。GDライブラリのインストールも必要ないHelperとして提供されているため、ファイルを設置すればすぐに使用可能。
-
-
CakePHP 2.3で PDFを作成する方法を調査「mpdf」「TCPDF」「FPDF」
CakePHPで PDFを編集、出力するには「mpdf」「TCPDF」「FPDF」といったプラグインがあり、使い勝手を比較検討しました。TCPDFが一番良さそうでした。
-
-
CakePHP3の1対多での連携を中間テーブルを使った多対多の連携に変更するときの手順
CakePHP3で「1対多」の連携を中間テーブルを利用した「多対多」の連携に変更するときの手順のまとめ。中間テーブルの設定やModelの変更などを間違いやすい箇所を指摘しながらの解説。
-
-
CakePHP 2.3 ログイン、操作履歴、アクセスログ出力
CakePHPでログインや操作履歴などのアクセスログ出力処理を作成します。
-
-
CakePHP 2.3でファイルをアップロード・その2 ファイル名を乱数で設定
CakePHPのアップロードするファイル名を乱数で変更しセキュリティを高める方法を解説。
-
-
CakePHP3で環境変数を設定して本番環境と開発環境を分けて処理をする場合
CakePHP3で開発環境と本番環境とで違う設定ファイルを読み込ませて環境ごとに定数を切り替える方法を解説。Apacheのhttpd.confに環境変数を設定し、それを読み込み判別する。
-
-
CakePHP3で Ajaxを使う方法の解説。3.6以降対応。Successとthenの両方を解説。
CakePHP3でajaxを利用する処理の実装方法を解説。プルダウンを変更するとデータベースの値を取得し検索結果の内容を変更するというような処理を想定。CakePHP3.6以降の CSRF対策対応済。
-
-
CakePHP3のインストールでURL rewriting……のエラーが!その解決方法解説
CakePHP3をインストールしたら URL rewriting is not properly configured.のエラーが。原因は.htaccessが有効になっていないこと。httpd.confに設定を追加すればOK。