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 2.x　3.x

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

Message コメントをキャンセル

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

: CakePHP 2.3で saveの便利な使い方・サンプルソース付き

CakePHPのレコードを保存、更新する際に使う Saveを詳細解説します。

: CakePHP3にWYSIWYGエディタのCKEditor4を設置、カスタマイズ方法を解説

WYSIWYGエディタであるCKEditor4をCDNを利用して簡単にCakePHP3に導入する方法とカスタマイズする方法を解説。CakePHP3にはページごとの振り分けを行うブロック化を利用する。

: CakePHP3の検索プラグイン「friendsofcake/search」の様々な検索の仕方の実装方法

CakePHP3で検索をするプラグイン「friendsofcake/search」の検索条件のカスタマイズ方法の解説。検索項目を増やしたり、以上、以下での検索や、チェックボックスによる検索の方法などを解説。

: CakePHP3でcomposerを利用してライブラリ・プラグインをインストールする方法

CakePHP3でcomposerを使ってパッケージ（ライブラリ、プラグイン）をインストールする方法の解説。PHPを使うのはCakePHP3が初めてというような方への使い方から解説。

: CakePHP3でシェルを作成しコマンドラインから実行・CakePHP2との違い

CakePHP3のシェルスクリプトを作成し、コマンドラインから実行する方法を解説。複数単語をつなげる場合の対応方法がCakePHP2より制限が厳しくなったのでCakePHP3の命名規則の確認が必要だ。

: CakePHP3で生の SQLの実行はConnectionManagerを使う

CakePHP3で生の SQL文を実行する方法を解説。クリエビルダーを使う場合は TableRegistryを利用するが、SQLを実行する場合は ConnectionManagerを使う。プリペアードステートメントの使用方法も解説。

: cakephp3 カスタムバリデーションを簡易的に実装する方法

CakePHP3の独自のバリデーションをテーブルクラス内に簡単に記述する方法を解説。他のテーブルクラスでは使えないが、記述する量は少なく実装できるため、他で使わない処理を書くのには便利。

: CakePHP 2.3　bakeの超初心者向けフォロー講座

CakePHP 2.3　bakeの超初心者向けフォロー講座

: CakePHP3でComposerでインストールできないプラグイン、外部ライブラリを vendorに入れて手動で読み込む方法

CakePHP3にComposerからインストールできないプラグインやライブラリなどを利用する方法、vendorにファイルを設置し、composer.jsonを更新し、それを呼び出す方法を詳細に解説。