アナログCPU：5108843109

ゲームと音楽とプログラミング（酒と女とロックンロールのノリで）

('ω') ＜ ｲｻﾞﾕｹｴﾝｼﾞﾆｬｰ

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

PHP CodeIgniter CodeIgniter入門シリーズ

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


正直PHPよりSQLの方が得意なくらいでクエリビルダは大嫌いなんですが、
仕事で使うことは多い（のにいまいちわかっていない）ので
ひたすら公式マニュアル見て書いて動かしてみました。
クエリビルダクラス — CodeIgniter 3.2.0-dev ドキュメント
いろいろ動かしてみたのを全部書くと上記の公式マニュアルと大体同じになっちゃうので、軽くまとめ直してみました。
それでも長くなったので今回はSELECT編。

今回やること

  • クエリビルダを使ってSELECTしてみる
  • WHERE, LIMIT, ORDER, JOIN, 集計系などある程度カバーする

先に書いておくと

今回と次回でクエリビルダの書き方をいろいろ試していますが、
仕事ではともかく、趣味の開発ではSQL直書きでいくことにします。

上記公式マニュアルには、

クエリを自分で書きたい場合は、データベース設定ファイルでこの クラスを使用できないようにすることもできます。そうすることで、コアの データベースライブラリとアダプタに、少ないリソースを有効活用させることができます。

とありますので、クエリビルダを使わない場合は config/database.php で

// クエリビルダを使わないのでFALSEにする
// $query_builder = TRUE;
$query_builder = FALSE;

こうしてやればよさそうです。

とりあえず

まあそういうわけで「SQL直書きで良くねえ？」という感想です。

例えば、「SELECT * FROM `user`」なら

// SQL直書き
$result1 = $this->db->query("SELECT * FROM `user`")->result();
// クエリビルダ
$result2 = $this->db->get('user')->result();

となり、まあ短いしテーブル名だけ書けばいいだけだし…というわけですが…

…いや、実務でそんなクエリほとんど書かないじゃん？

ややこしいクエリになればなるほど、クエリビルダではわけがわからない見た目になる印象でした。
でもやってみる。

現実的（実務で使う程度）なレベルの簡単なSELECT

とりあえずSELECTしてみる

実務で使う程度に簡単なクエリといえばこれくらいですかね。
退会していないユーザーをID降順に50件取得、という感じです。
ページャ想定しての50件として、全部で何件あるかも知れればいいですね。

SELECT `id`, `name`
FROM `user`
WHERE `delete_flag` <> 1
ORDER BY `id` DESC
LIMIT 50, 0

これをクエリビルダで書いてみるとこうなりました。

$this->db->select("id, name");
$this->db->from("user");
$this->db->where("delete_flag <>", 1); // 比較演算子はイコールなら省略できますが、それ以外ならこんな感じになります
$this->db->order_by("id", "DESC"); // ※第二引数は省略するとASCになります
$this->db->limit(50);
$this->db->offset(0);
$result = $this->db->get()->result(); // $result に戻り値が入る

うーん…？
既に微妙な感じが…。
もう少し省略することも可能なのですが（from, limit, offsetは省略してgetの引数に入れることもできる）
省略すればするほど、省略できない書き方になったときに色んな書き方が混在しやすくなります。

全部で何件あるかを調べるには

クエリビルダではできなさそうでした（さっそくつまづく）
やり方あるのかな…ご存じの方は教えてください。
ということでただのMySQLの話になってしまうのですが、以下のように書き換えればOKです。

$this->db->select("SQL_CALC_FOUND_ROWS `id`, `name`", FALSE); // ここを書き換え
$this->db->from("user");
$this->db->where("delete_flag <>", 1);
$this->db->order_by("id", "DESC");
$this->db->limit(50);
$this->db->offset(0);
$result = $this->db->get()->result();
$count_all = $this->db->query('SELECT FOUND_ROWS() AS cnt')->row()->cnt; // $count_all に全件数

selectの第二引数のFALSEは、簡単に言うと第一引数のカラム名をバッククォートでくくるかどうかです。
これを省略（TRUE）していると、内部では「SELECT `SQL_CALC_FOUND_ROWS` `id`, `name` ... 」となってしまいます。
なのでここではFALSEを指定の上、必要なところは自分でバッククォート入れています。

参考： [CodeIgniter] SQL_CALC_FOUND_ROWSを使って一覧と件数を一発で取得する │ M0DE

SELECTで取得してくるカラムやFROMで指定するテーブルに別名を付ける

select内やfrom内にベタ書きでOKでした。

// SELECT `A`.`id` AS `A_id`
// FROM `user` AS `A`
$this->db->select("A.id AS A_id");
$this->db->from("user AS A");
複数指定したいときは…

例えば、WHERE句で複数条件をANDでつないで指定したいとき。
ORDER句やGROUP句の中身を複数並べたいとき。
SELECTの中身が長くなるのでバラして書きたいとき。
こんな感じで並べるだけでOKでした。

// SELECT `id`, `name`
// FROM `user`
// WHERE `delete_flag` <> 1
//   AND `address` = 'tokyo'
// ORDER BY `id` DESC
//         ,`age` ASC
// LIMIT 0, 50
$this->db->select("id");
$this->db->select("name");
$this->db->from("user");
$this->db->where("delete_flag <>", 1);
$this->db->where("address", "tokyo");
$this->db->order_by("id", "DESC");
$this->db->order_by("age", "ASC");
$this->db->limit(50);
$this->db->offset(0);
複数指定したいときは…　その2

配列を渡せるケースもあります。

// WHEREだけ抜粋：WHERE `age` < 20 AND `address` = 'tokyo'）
$this->db->where(array("age <" => 20, "address" => "tokyo"));
SQL直書きできるケースも多い
$this->db->select("id, name");
$this->db->from("user");
$this->db->where("delete_flag <> 1 AND address = 'tokyo'");
$this->db->order_by("id DESC, age ASC");
$this->db->limit(50);
$this->db->offset(0);

個人的には「は？？？」って印象でしかないのですが
このような感じで引数にSQL文をそのまま渡すこともできることが多いようです。

WHERE句のバリエーションがすごい

id = 1
$this->db->where("id", 1);
id > 1
$this->db->where("id >", 1);
id > 1 AND age < 20
$this->db->where("id >", 1);
$this->db->where("age <", 20);
id > 1 OR age < 20
$this->db->where("id >", 1);
$this->db->or_where("age <", 20);

えっこれ気持ち悪くない？自分だけ？

id IN (1, 2, 3)
$this->db->where_in("id", array(1, 2, 3));
id IN (1, 2, 3) OR address IN ('tokyo', 'osaka')
$this->db->where_in("id", array(1, 2, 3));
$this->db->or_where_in("id", array("tokyo", "osaka"));
id NOT IN (1, 2, 3) OR address NOT IN ('tokyo', 'osaka')
$this->db->where_not_in("id", array(1, 2, 3));
$this->db->or_where_not_in("id", array("tokyo", "osaka"));

or_where_not_inて。

部分一致検索
// 名前に「ほげ」を含み、住所が「東京」で始まり、メールアドレスが「@gmail.com」で終わる
// （name LIKE '%ほげ%' AND address LIKE '東京%' AND mail LIKE '%@gmail.com'）
$this->db->like('name', 'ほげ', 'both');
$this->db->like('address', '東京', 'after');
$this->db->like('mail', '@gmail.com', 'before');

第三引数は、bothの場合は省略できる模様。
もう書かないけど、whereと同じようなノリで or_like, not_like, or_not_like もあります。

集計やグルーピング

GROUP BY
// SELECT group FROM user GROUP BY group
$this->db->select("group")
$this->db->from("user")
$this->db->group_by("group");
DISTINCT
// SELECT DISTINCT group FROM user
$this->db->select("group");
$this->db->from("user");
$this->db->distinct();
集計関数を使う

MIN, MAX, AVG, SUMといった集計関数もちゃんと用意されてました。

// SELECT MAX(age) AS max, MIN(age) AS min, ...
// FROM user
// GROUP BY `group`
$this->db->select("group"); 
$this->db->select_max("age", "max"); // 第二引数は別名（MAX(age) AS max）
$this->db->select_min("age", "min");
$this->db->select_avg("age", "avg");
$this->db->select_sum("age", "sum");
$this->db->from("user");
$this->db->group_by("group");

…COUNT関数は…？（調べてみても見当たらなかった）
仕方ないのでベタ書き戦法です。かゆいところに手が届かないなあ。

$this->db->select("COUNT(`id`) AS `count`", FALSE);
$this->db->from("user");
$this->db->group_by("group");
HAVINGする

HAVINGはサブクエリ使って書くより遅くなりがちなんですけど
HAVING句の使い方と速度検証 - アナログCPU：5108843109
クエリビルダだと複雑なクエリが余計に読みにくくなるし書くのも嫌になってくるので、もうHAVINGでいいかって気になってくる。
いいのかそれで。

// グループごとの合計人数が5人より多いレコードだけSELECT
$this->db->select("COUNT(`id`) AS `count`", FALSE);
$this->db->from("user");
$this->db->group_by("group");
$this->db->having("count >", 5);

WHEREと同じく、比較演算子がイコールなら省略してOKです。

そしてやっぱりor_havingも存在する。

JOINする

// SELECT user.id AS user_id, log.id AS log_id
// FROM user
// LEFT JOIN log ON user.id = log.user_id
$this->db->select("user.id AS user_id, log.id AS log_id");
$this->db->from("user");
$this->db->join("log", "user._id = log.user_id", "left");

join句の第三引数で「left」を指定していますが、もちろん必要に応じてinnerも指定できます。
rightとかもあるけど使わんかろ。

複数JOINする場合は例によって並べるだけでした。

// SELECT user.id AS user_id, log.id AS log_id, log_category.id AS log_category_id
// FROM user
// LEFT JOIN log ON user.id = log.user_id
// LEFT JOIN log_category ON log.category = log_category.id
$this->db->select("user.id AS user_id, log.id AS log_id");
$this->db->from("user");
$this->db->join("log", "user._id = log.user_id", "left");
$this->db->join("log_category", "log.category = log_category.id", "inner");

また、ON句の条件を複数にしたい場合は…クエリビルダっぽい書き方は見つけられませんでした。
ベタ書きで動きます…。

$this->db->join("log", "user._id = log.user_id AND delete_flag <> 1", "inner");

とても複雑なクエリを書く

めんどくさいので公式マニュアルからの引用です。

クエリのグルーピングでは、 WHERE 句を括弧で囲むことでグループを作ることができます。 これにより複雑な WHERE 句のクエリを作ることが可能です。
例:

$this->db->select('*')->from('my_table')
        ->group_start()
                ->where('a', 'a')
                ->or_group_start()
                        ->where('b', 'b')
                        ->where('c', 'c')
                ->group_end()
        ->group_end()
        ->where('d', 'd')
->get();

// 次のようになります:
// SELECT * FROM (`my_table`) WHERE ( `a` = 'a' OR ( `b` = 'b' AND `c` = 'c' ) ) AND `d` = 'd'

お、おう…。
引数にクエリをベタ書きするのを避けるならこうなるんですかね。
これくらいのネストができるクエリって結構あると思うんですけど、慣れてないと読みにくいっすね…。

参考文献

CodeIgniter 3 Query Builder – Lalcs

クエリビルダが嫌いな理由

突然の愚痴

データベースのエラーとかスローログとかを元に調査するとき、検索性が悪くて困る…。
自分が書いたコードなら大体の場所は見当つくかもしれないけど、そうでないと結構むずい。
これがSQL直接書いてるだけなら特徴的な部分を抜き出して一発grepかけるだけなんだけど。
何かいい方法あるのかなこれ。

あと、クエリビルダ自体が悪いわけじゃないんだけど、
こっちのほうが書きやすい・見やすいって言う人は「そもそもSQLやデータベースそのものがよく分かっていない」っていうケースが多い。
SQLを理解せずに見よう見まねのクエリビルダで書かれるとヤバいコードが生産されがちで困る。
（ありがちなのが、JOINの概念が分からなかったのか適当にSELECTしてきたデータをアプリ側でつなぎ合わせてるとか、集計関数を知らないのか同じくアプリ側で集計しなおしてるとか）
データベースの知見がある上でクエリビルダを使いこなしている方も当然いるんだろうけど、いくつかの現場を見た限り出会うことはなかったのでたぶん少数派。


うーん、使うメリットがわからん…。今は嫌いだから使いたくないけどメリットがあるのならもっと考えたいから誰か教えてくれ。
データベースの種類ごとまるっと乗り換える（MySQLからPostgreSQLにするとか）場合は修正箇所が少なくて済む、とかもあるんだろうけど、日頃の使い勝手悪い方が困るわ。そんな機会そうそうないでしょ。
（と言いつつ今やってる案件ではあるんですが（乗り換えではないけど、ほとんど同じシステムのDB違い版がある）結局レアケースだろうし…）
MySQLだろうがOracleだろうが書き方が変わらないので覚えることが少なくて済む…かもしれないけど、クエリレベルの話なんだからせいぜい方言差だし、どっちかというとフレームワークごとのクエリビルダの違いの方が大きいんじゃないのか？？
SQL知らなくても使えるっていうのは先述のとおりメリットよりデメリットの方が大きそうだし。