CakePHP 2.3でOn Duplicate Key構文を実装

2014/05/09 2018/08/04

CakePHPで On Duplicate Key構文を実装

On Duplicate Key構文とは

　
On Duplicate Key構文とは、Insertしようとした際、Unique項目に重複する値を指定していた場合、Insertせずに Updateを実行するための構文です。
似たような構文として「Replace Into」構文もあります。
※データベースは、MySQLを想定しています。
　
On Duplicate Key構文については「Insert On Duplicate Key Update構文の使い方」という記事も書いていますので、そちらもご覧ください。
　
　
また、この記事は、「CakePHP 2.3 主キー（ID）以外のキーで更新をする方法」の実験を行ったテーブルを続けて利用していますので、もし、サンプルプログラムを実装する際は、こちらの記事を参考にしていただき、テーブルの構築、プログラムの設置を行ってください。
　
さらに、続きの記事として「CakePHP 2.3で saveの便利な使い方・サンプルソース付き」もありますので参照ください。
　
また、MySQLのユニークキーには最大バイト数の制限がありますので、そちらは「MySQLのInnoDBでUniqueキーは最大767バイト」の記事をご覧ください。
　
　
この記事は、CakePHP2.3での実装方法ですが、CakePHP3での実装方法については、下記の記事を参考にしてください。
CakePHP3で生の SQLの実行はConnectionManagerを使う
　
　

PHPフレームワーク CakePHP 3入門

On Duplicate Key構文を CakePHPに実装する方法・その１

　
　
　CakePHPで　On Duplicate Key構文を実装する方法は
　見つかりませんでした。
　
　どなたか教えてください！
　
　
そのため、Queryメソッドを利用して、SQLを直接記述することにしました。
　
————————

public function edit4($id = null) {
  if (!$this->Comment->exists($id)) {
    throw new NotFoundException(__('Invalid comment'));
  }
  if ($this->request->is('post') || $this->request->is('put')) {
    $sani_list = "'" . Sanitize::escape( $this->request->data['Comment']['list'] ) . "'";
    $sql = "INSERT INTO comments ( post_id, sec_code, list ) values ( {$this->request->data['Comment']['post_id']}, {$this->request->data['Comment']['sec_code']}, {$sani_list} )
        ON DUPLICATE KEY UPDATE post_id = {$this->request->data['Comment']['post_id']}, sec_code = {$this->request->data['Comment']['sec_code']}, list = {$sani_list} ";
    if ( $this->Comment->query($sql) !== false ) {
      $this->Session->setFlash(__('The comment has been saved.' ));
      $this->redirect(array('action' => 'index'));
    } else {
      $this->Session->setFlash(__('The comment could not be saved. Please, try again.' ));
    }
  } else {
    $options = array('conditions' => array('Comment.' . $this->Comment->primaryKey => $id));
    $this->request->data = $this->Comment->find('first', $options);
  }
}

public function edit4($id = null) {

if (!$this->Comment->exists($id)) {

throw new NotFoundException(__('Invalid comment'));

}

if ($this->request->is('post') || $this->request->is('put')) {

$sani_list = "'" . Sanitize::escape( $this->request->data['Comment']['list'] ) . "'";

$sql = "INSERT INTO comments ( post_id, sec_code, list ) values ( {$this->request->data['Comment']['post_id']}, {$this->request->data['Comment']['sec_code']}, {$sani_list} )

ON DUPLICATE KEY UPDATE post_id = {$this->request->data['Comment']['post_id']}, sec_code = {$this->request->data['Comment']['sec_code']}, list = {$sani_list} ";

if ( $this->Comment->query($sql) !== false ) {

$this->Session->setFlash(__('The comment has been saved.' ));

$this->redirect(array('action' => 'index'));

} else {

$this->Session->setFlash(__('The comment could not be saved. Please, try again.' ));

}

} else {

$options = array('conditions' => array('Comment.' . $this->Comment->primaryKey => $id));

$this->request->data = $this->Comment->find('first', $options);

}

————————
　
変更点は、6行目～9行目です。うち、SQL文は 7行目、8行目です。
　
SQL文はサニタイズする必要があることと、全角文字が入る個所は「’（シングルクォート）」で囲まないとエラーになりますので、6行目でその処理をしています。
9行目も少し変更しています。これは、Queryメソッドの戻り値が「true／false」だけではないためです。
　
Queryメソッドの戻り値は下記の様になります。
　false：SQLにエラーがある場合
　array()：SQLが正常に処理されたが、値が返ってこなかった場合
　取得した値：SQLが正常に処理され、値が返ってきた場合
　
そのため、今回の様に Select文ではない SQLを実行した場合の戻り値は、正常に終了した場合は「array()」となりますので、上記のサンプルのような if文になっています。
　
また、SQLに記述する項目の順番は特に気にする必要はないようです。Uniqueキーに設定した項目を先に書く必要もないようです。
　
また、間違いやすい点としては、7行目、9行目のテーブル名です。
7行目では「comments」と書いていますが、SQL文を直接実行しますので、CakePHPのテーブルの指定と違い実際に存在するテーブルの名称をそのまま記述します。
しかし、9行目の「Comment」の部分は、CakePHPで処理をする部分ですので、CakePHPの規約に従い記述します。
　
　

ON DUPLICATE KEY構文を CakePHPに実装する方法・その２

　
SQL文を直接記述する方法で実装する場合、bind機能を使って実装する方がスマートに実装できます。
エスケープ処理もする必要がありません。
　
————————

public function edit5($id = null) {
  if (!$this->Comment->exists($id)) {
    throw new NotFoundException(__('Invalid comment'));
  }
  if ($this->request->is('post') || $this->request->is('put')) {
    $sql = "INSERT INTO comments ( post_id, sec_code, list ) values ( ?, ?, ? )
        ON DUPLICATE KEY UPDATE post_id = ?, sec_code = ?, list = ? ";
    $post_id  = $this->request->data['Comment']['post_id'];
    $sec_code = $this->request->data['Comment']['sec_code'];
    $list     = $this->request->data['Comment']['list'];
    if ( $this->Comment->query($sql, array ($post_id,$sec_code,$list,$post_id,$sec_code,$list), false ) !== false ) {
      $this->Session->setFlash(__('The comment has been saved.' ));
      $this->redirect(array('action' => 'index'));
    } else {
      $this->Session->setFlash(__('The comment could not be saved. Please, try again.' ));
    }
  } else {
    $options = array('conditions' => array('Comment.' . $this->Comment->primaryKey => $id));
    $this->request->data = $this->Comment->find('first', $options);
  }
}

public function edit5($id = null) {

if (!$this->Comment->exists($id)) {

throw new NotFoundException(__('Invalid comment'));

}

if ($this->request->is('post') || $this->request->is('put')) {

$sql = "INSERT INTO comments ( post_id, sec_code, list ) values ( ?, ?, ? )

ON DUPLICATE KEY UPDATE post_id = ?, sec_code = ?, list = ? ";

$post_id = $this->request->data['Comment']['post_id'];

$sec_code = $this->request->data['Comment']['sec_code'];

$list = $this->request->data['Comment']['list'];

if ( $this->Comment->query($sql, array ($post_id,$sec_code,$list,$post_id,$sec_code,$list), false ) !== false ) {

$this->Session->setFlash(__('The comment has been saved.' ));

$this->redirect(array('action' => 'index'));

} else {

$this->Session->setFlash(__('The comment could not be saved. Please, try again.' ));

}

} else {

$options = array('conditions' => array('Comment.' . $this->Comment->primaryKey => $id));

$this->request->data = $this->Comment->find('first', $options);

}

————————
　
戻り値の処理や、テーブル名称の気を付ける点はその１の時と同じです。
　
11行目の queryで渡している引数の 3つ目は、キャッシュを OFFにするための設定です。デフォルトは ONですので、OFFにしたい場合に記述します。
　
SQL文の中や、Queryメソッドの処理で引数に配列を与えるところで、「,」の数があっていなかったり、配列の最後に「,」が付いていたりする場合は、エラーの原因になりますので、エラーが出る場合はそんなところも確認してみてください。
　
　
この記事を書くにあたっては、下記のサイトを参考にさせていただきました。
CakePHP1系では、CakePHPが処理をする疑似バインド機能だったようですが、CakePHP2系になるとプリペアドステートメント（Prepared Statement）になっているようです。
　http://d.hatena.ne.jp/cakephper/20090417/1239939705
　http://d.hatena.ne.jp/cakephper/20120204/1328324327

- CakePHP 3.x 4.x 5.x

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

Message コメントをキャンセル

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

: CakePHP3で Ajaxを使う方法の解説。3.6以降対応。Successとthenの両方を解説。

CakePHP3でajaxを利用する処理の実装方法を解説。プルダウンを変更するとデータベースの値を取得し検索結果の内容を変更するというような処理を想定。CakePHP3.6以降の CSRF対策対応済。

: CakePHP3のHtmlHelperのLink設定のまとめ。mailto、URL、Root／Homeのリンクなども

CakePHP3でHtmlHelperを使ってリンクの設定をする方法のまとめ。基本形からURLを指定、class、id、targetを指定、mailtoのリンク、画像をアンカーに、JavaScriptのダイアログなどの解説。

: CakePHP 2.3　Search Pluginで検索処理　その７queryを使って日付の範囲検索

CakePHPの検索プラグイン Search Pluginの検索処理の中で queryを使って日付の範囲検索の方法です。

: CakePHP3でテーブルにカラム（項目）を追加したときに変更するポイントのまとめ

CakePHP3でシステム開発をする際、途中でカラムを追加した場合に何を変更すればいいかを確認。カラムを追加する前後で Bakeした結果を比較し、変更になった点をリストアップした。

: CakePHPで favicon.icoやapple-touch-icon-144-precomposed.pngが could not be foundのエラーが出るときの対処方法

CakePHPで「CakeDC/Users」などルーティングを行うプラグインを利用するときに、favicon.icoやapple-touch-icon-144-precomposed.pngがNotFoundエラーになることがある。その対処方法の解説。

: CakePHP4のcake cache clear_allでPermission deniedはパーミッションの変更が必要

CakePHP4でキャッシュをクリアするコマンドcake cache clear_allでPermission deniedのエラーが出るのはcakeにパーミッションが足りないとき。その対処方法を解説。

: CakePHP3でデフォルトのソート条件を設定してユーザの選択肢たソート条件を有効にする方法

CakePHP3でデフォルトのソート条件を設定しつつ画面上でユーザがソート条件を選択したときもソート処理を実行させる方法を解説。ソート条件はページネーションの処理として実装。

: CakePHP3、CakePHP4のキャッシュをクリアする方法「bin/cake cache clear_all」を使う

CakePHP3、CakePHP4では処理を高速化する手法の一つとしてキャッシュを利用している。しかし、その情報は元の情報を更新しても反映されない場合がある。そんなときはキャッシュを削除する必要がある。

: CakePHP4のフラッシュメッセージの表示場所、デザインを変更する方法を解説

CakePHP4のエラーメッセージ、完了メッセージなどを表示するフラッシュ処理の解説。Controller、レイアウトファイル、テンプレートファイルでそれぞれ処理を指定する。

: CakePHP3で静的ページの作成は webrootか pagesを使う。トップページを参考に解説

CakePHP3で静的なページを設置する場合の方法（webrootとpagesとを活用する方法）を解説。pagesの解説はデフォルトのトップページがどう表示されているかを参考に解説。ルーティングの機能も。