CodeIgniter入門 #8：データベースの操作＜クエリビルダ編・更新系の巻＞

CodeIgniter入門シリーズカテゴリーの記事一覧 - アナログCPU：5108843109

前回でだいぶ疲れたのですがせっかくなので更新系もやっておきます。
参照系は基本的に「は？」と思ってたんですが、更新系は「これ上手く使うと効率上がるかも？」と思うところもちらほら。
まあ確かにSQLでもSELECTの方が厄介だよね、普通は…

公式マニュアルはこちら。
クエリビルダクラス — CodeIgniter 3.2.0-dev ドキュメント

今回やること

クエリビルダを使ってINSERT・UPDATE・DELETE・TRUNCATEしてみる

INSERT ... VALUES ...（1件のレコード追加）

$data = array(
    'name'     => "ほげほげさん",
    'login_id' => "username",
    'password' => "password"
);
$this->db->insert("user", $data);

おおお…わかりやすい。
これはPHPで書いてると相性良さそう。

オブジェクトも使えるようです。
（公式からの引用）

/*
class Myclass {
        public $title = 'My Title';
        public $content = 'My Content';
        public $date = 'My Date';
}
*/

$object = new Myclass;
$this->db->insert('mytable', $object);

INSERT ... VALUES ... （複数件のレコード追加）

$data = array(
    array(
        'name'     => 'ふがふがさん',
        'login_id' => 'user2',
        'password' => 'pass'
    ),
    array(
        'name'     => 'ぴよぴよさん',
        'login_id' => 'user3',
        'password' => 'pass'
    ),
);
$this->db->insert_batch('user', $data);

おおおお…

UPDATE（1件のレコード更新）

$data = array(
    'name'     => "ほげほげさん_update",
);
$this->db->where("id", 1);
$this->db->update("user", $data);

SELECTと同じく、whereを使って絞り込みができました。

UPDATE（複数件のレコード更新）

$data = array(
    array(
        'id'   => 3,
        'name' => "更新されるふがふがさん",
    ),
    array(
        'id'       => 4,
        'name'     => "更新されるぴよぴよさん",
        'password' => "newpass"
    )
);
$this->db->update_batch("user", $data, "id");

！！！
今回一番の感動。
めんどくさいbulk-updateをいい感じに作ってくれます。
第一引数がテーブル、第二引数がデータなのはこれまで通りで、第三引数に渡したカラム名に対応するデータを使ってくれます。
あっこれならクエリビルダ使ってもいいかも！と思いましたが、しかしbulk-updateやる機会はそんなにないな…。

-- ↑は↓を作ってくれます！！！！！すごい！！！
UPDATE `user` 
SET
  `name` = CASE 
     WHEN `id` = 3 THEN '更新されるふがふがさん'
     WHEN `id` = 4 THEN '更新されるぴよぴよさん'
    ELSE `name`
  END
 ,`password` = CASE
    WHEN `id` = 4 THEN 'newpass'
    ELSE `password`
  END
WHERE `id` IN(3,4)

あればUPDATE、なければINSERT

※タイトル詐欺です。ちゃんと読んでね。

$data = array(
    'id'       => 1, // ここがPK
    'name'     => "ほげほげさん_update_or_insert",
);
$this->db->replace("user", $data);

渡されたデータの中に、primaryかuniqueのカラムがあれば、それを元に「あればUPDATE、なければINSERT」をやってくれます。
やったー超簡単！

…

……

…………ん？ replace？

はい、実際に発行されるクエリは「REPLACE INTO ... 」でした。
これはMySQLにおいて「（あろうがなかろうが）DELETEしてINSERT」する構文です。

例えばレコード作成日時をデフォルトで現在日時にしているとREPLACEした日時にすげ変わりやがりますし、
on updateだって当然反応します。
律儀にアプリ側で日時を渡して記録しているならともかく、ある程度DB任せの設定の場合は危ないのでご注意を。

完全にMySQLの話なので参考URLの紹介にとどめておきますが、
基本的には「INSERT ... ON DUPLICATE KEY UPDATE」構文をおすすめします。
（今回は検証しませんがREPLACEの方が速いという説もありますので、よーーーーく検討して選んでください）

で、ON DUPLICATE... するにはクエリビルダで用意されていないようなので、自分で拡張する必要があるようです。
~~やっぱりSQLベタ書きでよくない？~~
【CodeIgniter3】クエリビルダーにINSERT ON DUPLICATE KEY UPDATE構文によるバッチ処理を追加する方法 - あずみ.net

DELETE

さすがにDELETEはシンプルでした。

// DELETE FROM `user` WHERE `id` = 3
$this->db->delete('user', array('id' => 3));

条件の指定方法はWHEREと同じのようで、

$this->db->where("id", 3);
$this->db->delete("user");

でもOKでしたし、なんならFROM句も分離できました。

$this->db->from("user");
$this->db->where("id", 3);
$this->db->delete();

全データ削除

「$this->db->delete("user");」でええんちゃうんかと思ったらダメでした。（うっかり防止？）

ご丁寧に全消し専用メソッドが用意されています。

// DELETE FROM `user`
$this->db->empty_table("user");

TRUNCATEもあります。

// TRUNCATE `user`
$this->db->truncate("user");

DELETEとTRUNCATEの違いはこれまたMySQLの話なので軽くにしておきますが、
DELETEは「レコードを削除する」で
TRUNCATEは「テーブルを作り直す」です。
TRUNCATEの方が速くてキレイになりますが、トランザクションが効きません。
適切な方を選んでください。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.1.33 TRUNCATE TABLE 構文

やっぱりかゆいところに手が届かない…

「INSERT ... SELECT ...」とか「UPDATE ... LEFT JOIN ... SET ...」とかやりたいんですけど一体どうすれば…？
調べてみても有力な情報が見当たらないし、いろいろ試してもみましたが上手くいきませんでした。うーんいまいち。