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でPHP Simple HTML DOM Parserを使ってスクレイピングする方法
CakePHP3でPHP Simple HTML DOM Parserを使ってスクレイピングをする方法を解説。インストール方法、読み込み方法。および、具体的なスクレイピングを実行するサンプルソースも。
-
-
CakePHP4のCakeDC/Usersの画面、メール本文テンプレートのカスタマイズ方法解説
CakeDC謹製Usersプラグインの紹介。ユーザ新規登録の流れを紹介しつつ、テンプレートファイル、設定情報ファイルの場所とそれらをカスタマイズする方法を説明します。
-
-
CakePHP3でユーザ定義の定数、変数を設定し、読み込む方法解説
CakePHP3で定数や共通で使う変数をまとめて設定し、プログラム内で読み込む方法を、bootstrap.phpに直接記述する方法と定数ファイルを分ける方法の3つの方法で解説。
-
-
CakePHP3にデザインテンプレートBootstrapを導入する方法・friendsofcake/bootstrap-ui使用
CakePHP3にプラグイン「friendsofcake/bootstrap-ui」、デザインテンプレート「Bootstrap」を設置する手順を解説。Bootstrapの簡単な使い方やデフォルトのデザインとの混在方法なども解説。
-
-
CakePHP4のCakeDC/Usersのログイン時のリダイレクトとユーザ権限管理の設定解説
CakeDC謹製UsersプラグインのCakePHP4版の紹介。ログイン認証後にリダイレクトする先の設定方法についての解説と実運用するために必要なコツを解説。便利な仕組みも仕様の理解があって初めてうまく使いこなせる。
-
-
CakePHP 2.3 ID以外のカラムでアソシエーション(連携)をさせる場合
ID以外のカラムでアソシエーション(連携)させるための考え方とサンプルソースを用いての説明を行っています。
-
-
VirtualBoxにCakePHP3を設置。必要なCentOS、Apache、PHP、MySQL、Composerをインストールし設定する
VirtualBoxにCentOS、Apache、MySQL、PHPをインストールするところから初めてCakePHP3の開発環境を構築する手順を詳細解説。この記事1つで全ての設定が完了する。
-
-
CakePHP3のfriendsofcake/searchでツリーカテゴリーの子階層も含めて検索する方法
CakePHP3のツリービヘイビアを使ったツリーカテゴリーの子階層も含めての検索を検索プラグイン「friendsofcake/search」を使って実現する方法を解説しました。
-
-
CakePHP3で画像・ファイルのアップロード処理を自作・解説付き・その2
ファイルのアップロード機能の自作サンプルコードとその解説のその2。アップロード機能に関連するファイルの更新や削除の処理や画像、フォルダのパスの指定方法などを含めて解説。
-
-
CakePHP3にデイトピッカー jQuery UI DatePickerを実装する手順の解説
CakePHP3にjQuery UIのDatePickerを実装する手順を説明。併せて、デイトピッカーを設置に関連するCakePHP3の解説と、テーマを変更したり、表記を変更するカスタマイズする方法なども紹介。