Hatena::ブログ(Diary)

Dazing days このページをアンテナに追加 RSSフィード

2012-12-22

QueryBuilderで小難しいSELECT文を書くためのノウハウ #FuelPHPAdvent2012


この記事は FuelPHP Advent Calendar 2012 の 22日目の記事です。
昨日は@さんの「FuelPHPで1サイトを作ってみて気が付いた点など」でした。

あさのひとしと申します。
今回はタイトルの通り、FuelPHPのQueryBuilderを使いこなすために悪戦苦闘した結果得られたエッセンスをノウハウとしてまとめてみました。
少し長いですが、どうぞよろしくお願い致します。

はじめに

さて、みなさんは普段どれくらいの「SQL文」を書かれるでしょうか?
ORMが登場して、ほとんどSQL文自体を書くことは少なくなったのかなと思います。

ただ、時には効率重視にする必要がある場合もあります。
その場合はDBからデータを取得して処理するのではなく、できる限りの処理をデータベース側に行わせたいものです。

そういったときにサブクエリが効いてくるわけですが、これをFuelPHPのQueryBuilderで表現するには?というのがこの記事の目的です。
ORMの有用性はご存知のとおりと思いますので、ここではORMには触れずに参ります。

以下すべてのソースコードは、PHP5.4のビルトインサーバ(PHP5.4.9)+FuelPHP 1.4、MySQLバージョン5.5.28にて動作確認済みです。
以下すべてのソースコードはそれ単体で動くものではなく、また著作権表示・許諾表示を怠っておりますが、MITライセンスを適用させて頂きます。


0.環境準備

まずは、まっさらのFuelPHP1.4環境を用意します。*1

どのようなSQL文が生成されているか分かりやすくなるよう、profilingをtrueにしておきましょう。
データベースのprofilingもtrueにしておく必要があります。

fuel/app/config/development/config.php
return array(
    'profiling' => true,
);
fuel/app/config/development/db.php
return array(
    'default' => array(
        'connection' => ({省略}),
        'profiling' => true,
    ),
);

今回は説明用に、oilを使用してテーブルを4つ作成します。
簡単な図書館の蔵書・貸出管理システムをイメージして頂ければと思います。

  • 出版社テーブル 出版社名
  • 書籍テーブル 書籍名と出版社ID
  • 貸出テーブル 貸出日時、書籍IDと会員ID
  • 会員テーブル 名前と誕生日

ということで、コマンドラインで以下を実行します。

oil g model publisher  name:string[10]
oil g model book  name:string[20] publisher_id:int[11]
oil g model member  name:string[10] birth_date:date
oil g model rental  rental_dttm:datetime book_id:int[11] member_id:int[11]
oil r migrate

お手数ですが、これらのテーブルに適当にデータを投入して下さい。
ここまでで、環境準備は終了です。

1.QueryBuilderのおさらい


まずは、QueryBuilderについてのおさらいです。

$search_book_id = \Input::post('book_id');
$search_member_id = \Input::post('member_id');

$query = \DB::select('*')->from('rentals');

if ($search_book_id)
{
    // 検索条件に本のIDがあれば、where条件に追加
    $query = $query->where('book_id', '=', $search_book_id);
}
if ($search_member_id)
{
    // 検索条件に会員IDがあれば、where条件に追加
    $query = $query->where('member_id', '=', $search_member_id);
}

$query = $query->order_by('rental_dttm', 'desc');
$result  = $query->execute();

今更書くほどのことでもないと思いますが、条件に応じて動的にwhere条件を追加できるのが便利ですよね。

ただ、少し難しいSQL文、例えばMySQL関数を使おうとする場合には少しコツが必要になります。

2.MySQL関数を使うためのノウハウ

クエリビルダはエスケープ処理を自動でかけてくれるため、場合によっては困る場合があります。
MySQL関数を使う場合には、エスケープ処理が行われないようにする必要があります。
そういう場合に使用できるのが、DB::expr() メソッドです。

たとえばMAX関数を使う場合は、こんな感じになります。

$result = \DB::select(\DB::expr('MAX(`birth_date`)'))
    ->from('members')
    ->execute();

しかし実は、以下の書き方でもOKです。
バッククォートをダブルクォートにするのがポイントです。

$result = \DB::select('MAX("birth_date")')
    ->from('members')
    ->execute();

これはselect句に限られませんので、where句で書いてもOKです。
たとえば「40歳以上の会員を取得」するには、こんな感じで書けばOKです。

$result = \DB::select('*')
    ->from('members')
    ->where('FLOOR(CURDATE()+0 - DATE_FORMAT("birth_date", \'%Y%m%d\')+0 ) / 10000', '>=', '40')
    ->execute();

カラム名をダブルクォートで囲う。コレがポイントです。
適切にエスケープすることで、シングルクォートも使えます。

ここでひとつ注意点ですが、これらの方法を使う場合、エスケープ処理が行われません。
それはつまり、ユーザーが入力した内容をこの内部に反映させる場合、SQLインジェクション攻撃を受ける可能性があるという事になります。*2

ユーザー入力をDB::expr() メソッド内またはそれに準ずる方法で処理する場合は必ずDB::escape() メソッドを使用し、以下のように対策を行うようにして下さい。

// $date_strには、'20121222'のような文字列を期待する
$date_str = \Input::post('base_date_str');
$result = \DB::select('*')
    ->from('members')
    ->where('FLOOR('. \DB::escape($date_str). '+0 - DATE_FORMAT("birth_date", \'%Y%m%d\')+0 ) / 10000', '>=', '40')
    ->execute();

3.サブクエリを使うためのノウハウ

さて、それでは「A社とC社が発行している本についての蔵書、各々最後に貸し出した日付、借りた人物を取得」してみたいと思います。
普通はこんなにややこしい処理を一文で書くことは少ないかもしれませんが、
「データベースでできることはデータベースで」というポリシーで処理してみるとこんな感じになります。

$query = \DB::query(
    'select p.name as publisher_name, b.name as book_name, r.rental_dttm, m.name as member_name'.
    ' from books as b'.
    ' inner join publishers as p on b.publisher_id = p.id'.
    ' left outer join ('.
    '  select out_r.rental_dttm, out_r.book_id, out_r.member_id'.
    '  from rentals as out_r'.
    '  where (out_r.rental_dttm, out_r.book_id) in'.
    '   (select MAX(in_r.rental_dttm) as rental_dttm, in_r.book_id'.
    '    from rentals as in_r'.
    '    group by in_r.book_id)'.
    ' ) as r on b.id = r.book_id'.
    ' left outer join members as m on r.member_id = m.id'.
    ' where p.name in ("A社", "C社")'.
    ' order by p.id, b.id');
$result = $query->execute();

100行200行のSQL文を見慣れている方であれば大したことはないですが、SQL文に慣れていないかたはぎょっとするかも知れません。

それではこれをQBで書き換えてみましょう。

$query = \DB::select( array('p.name', 'publisher_name'),
        array('b.name', 'book_name'), 'r.rental_dttm', array('m.name', 'member_name'))
    ->from(array('books', 'b'))
    ->join(array('publishers', 'p'), 'inner')->on('b.publisher_id', '=', 'p.id')
    ->join(array(DB::expr('(select out_r.rental_dttm, out_r.book_id, out_r.member_id'.
        ' from rentals as out_r'.
        ' where (out_r.rental_dttm, out_r.book_id) in'.
        '  (select MAX(in_r.rental_dttm) as rental_dttm, in_r.book_id'.
        '   from rentals as in_r'.
        '   group by in_r.book_id) )')
            , 'r'), 'left outer')->on('b.id', '=', 'r.book_id')
    ->join(array('members', 'm'), 'left outer')->on('r.member_id', '=', 'm.id')
    ->where('p.name', 'in', array('A社', 'C社'))
    ->order_by('p.id')
    ->order_by('b.id');
$result = $query->execute();

これぐらい長い箇所がある場合は、無理にダブルクォートを使うより、素直にDB::expr() メソッドを使うことをおすすめします。
どうしてもDB::expr() メソッドを使いたくないという場合は、各カラム名をダブルクォートで囲むようにして下さい。

4.where句の右辺にMySQL関数を使いたい時のノウハウ

最後に、一番お伝えしたかったのがこのノウハウです。
where句の右辺にMySQL関数を使うと値扱いでクォートされてしまい、データを取得できません。

「直近30日の貸し出し履歴を書籍名、借りた人物込みで取得」する事を想定してみます。

$query = \DB::select('r.id', 'r.rental_dttm',
        array('b.name', 'book_name'), array('m.name', 'member_name'))
    ->from(array('rentals', 'r'))
    ->join(array('books', 'b'), 'inner')->on('r.book_id', '=', 'b.id')
    ->join(array('members', 'm'), 'inner')->on('r.member_id', '=', 'm.id')
    ->where('DATE(r."rental_dttm")', '>=', 'ADDDATE( CURDATE(), INTERVAL -30 DAY )')
    ->order_by('r.rental_dttm', 'DESC');
$result = $query->execute()->as_array();

この場合、where句は以下のように解釈されてしまいます。

  WHERE DATE(r.`rental_dttm`) >= 'ADDDATE( CURDATE(), INTERVAL -30 DAY )'

しかしこれも小技を効かせれば、解決可能です。

$query = \DB::select('r.id', 'r.rental_dttm', array('b.name', 'book_name'), array('m.name', 'member_name'))
    ->from(array('rentals', 'r'))
    ->join(array('books', 'b'), 'inner')->on('r.book_id', '=', 'b.id')
    ->join(array('members', 'm'), 'inner')->on('r.member_id', '=', 'm.id')
    ->where('DATE(r."rental_dttm") >= ADDDATE( CURDATE(), INTERVAL -30 DAY )', '=', true)
    ->order_by('r.rental_dttm', 'DESC');
$result = $query->execute()->as_array();

こうすると、where句が以下のように解釈されます。

  WHERE DATE(r.`rental_dttm`) >= ADDDATE( CURDATE(), INTERVAL -30 DAY ) = '1'

ぱっと見でわかりにくいのですが、「DATE(r.`rental_dttm`) >= ADDDATE( CURDATE(), INTERVAL -30 DAY )」がtrueのデータ、つまり「1」と評価できるデータが取得対象ということになります。
これは「DATE(r.`rental_dttm`) >= ADDDATE( CURDATE(), INTERVAL -30 DAY )」そのものとして処理される、という事になります。

この方法は、where in でサブクエリを使いたい場合にも応用が効くノウハウです。

(2013/09/10 追記)
そーだいさんによると、上記のように回りくどく書かなくとも以下の記述で問題ないそうです。
http://soudai1025.blogspot.jp/2013/09/fuelphp_9.html

//  WHERE DATE(r.`rental_dttm`) >= ADDDATE( CURDATE(), INTERVAL -30 DAY ) ←生成Query
$query->where('DATE(r."rental_dttm")', '>=', DB::expr("ADDDATE( CURDATE(), INTERVAL -30 DAY )"));

# 引用とご指摘、ありがとうございました!
(追記 ここまで)

おわりに

いかがでしたでしょうか?
FuelPHPにはマニュアルでは触れられていない、思わぬ機能がまだまだ隠されています。

当然ながらcoreのソースもPHPで書かれていますので、処理の中身を読むことができます。
気になる箇所があれば、時間を掛けて読み込んでみたいものです。
思わぬ発見があるかも知れませんよ?


この記事がFuelPHPでDBを扱う方にとって、お役に立てますと幸いです!

明日は@さんの「FuelPHP vs CodeIgniter」です。お楽しみに!

# mukakenさん、いつもはてなスターありがとうございます!
この場をお借りしてお礼申し上げます。

(2013/09/10 「4.where句の右辺にMySQL関数を使いたい時のノウハウ」について追記済み。)

*1FuelPHPの環境準備手順は省略します。

*2:ただし、私が試した限りはSQLインジェクション攻撃を引き起こすことはできませんでした。

SQL大好きSQL大好き 2014/02/16 23:57 ->where('FLOOR(CURDATE()+0 - DATE_FORMAT("birth_date", \'%Y%m%d\')+0 ) / 10000', '>=', '40')
でなぜSQLインジェクション攻撃対象になるのかわかりません。
詳しく説明していただけないでしょうか?
私の理解の範疇では、SQL文字列中にユーザーが故意に入力した値がSQLの場合、
作成者が意図しないSQLが出来上がってしまい、結果的にそのSQLが実行されることで
表示したくないデータが検索できたり、テーブルデータが破壊できたりすることだと
認識していますが、例の「CURDATE()」はmysqlのSQL関数なので、ユーザーの故意にSQLを挟み込む
余地がなく、単に処理分散化しただけだと思うのですが・・・

通りすがり通りすがり 2015/01/19 15:00
>ユーザーが入力した内容をこの内部に反映させる場合
と、*2の内容を読めば理解出来ると思います

通りすがり通りすがり 2015/01/19 15:00
>ユーザーが入力した内容をこの内部に反映させる場合
と、*2の内容を読めば理解出来ると思います

スパム対策のためのダミーです。もし見えても何も入力しないでください
ゲスト


画像認証

Connection: close