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

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

: CakePHP4で複数の引数（パラメータ）を付与してコマンドを実行する方法

CakePHP4でコマンドを実行する際に引数（パラメータ）をコマンド内で受け取る処理について解説。複数個の引数にも対応する記述方法も解説。

: CakePHP3でPHPExcelを使ってエクセルファイルを生成、出力する方法

CakePHP3でPHPExcelを利用してエクセルを編集、出力するサンプルソース＋解説。PHPExcelのインストール方法の解説からファイル保存とダウンロードの方法なども解説。

: CakePHP3のルーティング（routes.php）の変更が反映されない時はキャッシュのクリアを

CakePHP3でルーティングの設定変更をしたけど反映されない！そんなときは慌てず騒がずキャッシュをクリアしよう！ルーティングの設定もキャッシュされることがあるらしい。

: CakePHP3でQRコードを作成、表示するライブラリ「cakePHP-QR-Code-Helper」

CakePHPでQRコードを生成するライブラリ「cakePHP-QR-Code-Helper」の紹介。GDライブラリのインストールも必要ないHelperとして提供されているため、ファイルを設置すればすぐに使用可能。

: CakePHP 2.3　ID以外のカラムでアソシエーション（連携）をさせる場合の詳細ページの注意点

ID以外のカラムでアソシエーション（連携）させて詳細ページを表示させる際の考え方と注意点をサンプルソースを用いて解説しています。

: CakePHP3でCookieを保存、呼び出し、削除の操作・CakePHP3.7対応

CakePHP3.7でCookieを保存、取り出し、削除する方法を解説。CakePHP3でのCookieの取り扱いはバージョンごとに変更されるため、環境に合わせた方法を探す必要がある。

: CakePHP 2.x系　Viewでのコメントの記述方法など

CakePHPにおいて View、ctpファイルでコメントを記述する方法を解説。

: CakePHP3のCakeDC/Users、Authでログインなしでもアクセスを許可する設定

CakePHP3の Authコンポーネントや CakeDC/Usersプラグインなどを利用したユーザ管理・認証システムにおいて、ログインしていなくても見ることができるページの設定方法を解説。

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

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

: CakePHP3でDocumentRootやtmp、webroot、logsなどのフォルダへのパスの定数

CakePHP3で特定フォルダのパスの定数を解説。root、DocumentRoot、app、config、webroot、tests、tmp、cache、vendor、コア、コアの srcが設定済み。realpath()関数を使うと柔軟なパス指定が可能。