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

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

Message コメントをキャンセル

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

: CakePHP3のプラグイン「CakeDC/Users」を日本語化・翻訳ファイルもダウンロード可

CakePHP3のユーザ管理、ログイン認証プラグインである「CakeDC/Users」のメッセージを日本語にする手順の解説とともに、日本語の翻訳ファイルを提供。ファイルを設置すれば日本語になる！

: CakePHP3のタイムゾーンを協定世界時UTCから日本標準時間JSTにずれを変更する方法

CakePHP3の標準設定のタイムゾーンは「UTC（協定世界時）」に設定されている。これを日本標準時に変更する方法（app.php、bootstrap.phpの変更方法）の解説。

: CakePHP3の検索プラグイン「friendsofcake/search」の設置方法・CakePHP3.6対応

CakePHP3で検索を担うプラグイン「friendsofcake/search」の紹介。基本的な設置方法の紹介のほか、処理の記述方法のバリエーション、エラーの解説など。CakeDC/searchより導入は簡単！

: CakePHPで Auto Incrementを外すと Duplicate entry ‘0’ for keyのエラーが出るかも

CakePHPでAuto Incrementの設定を変更したときに「Duplicate entry ‘0’ for key」のエラーが出た。原因はModel内で IDを編集する処理の追加を忘れていたからだった。

: CakePHP3のfriendsofcake/searchでブックマークチュートリアルのタグ検索を実装

CakePHP3のCookbookにあるブックマークチュートリアル。ここで紹介されているタグで検索する処理を検索プラグイン「friendsofcake/search」で実現する方法を解説しました。

: CakePHP 2.3　Search Pluginで検索処理　その１設置方法

CakePHPの検索プラグイン Search Pluginの設置方法と基本的な検索処理の解説です。

: MySQL、CakePHP 2.3で「tinyint(1)」の Boolean型の動作を再確認

MySQL＋CakePHPの環境で「tinyint(1)」を利用する際の動作を検証。「tinyint(1)」の Boolean型について CakePHPでは自動処理が実施されていることを確認しました。