FuelBeans 23 : DBのスニペット

まとめ記事を書いていて、DBに関しては、マニュアルが翻訳されるまでのつなぎ記事を用意しておけばいいのに気づきました。

後は、翻訳されたらジッジリ自分の目で読んでもらえばいいんですからね。私自身、ドキュメントのサンプルで勉強し、そのサンプルをコードテンプレート（スニペット）として利用しています。もちろん、多少はまとめていますよ。

このチュートリアルで紹介した、私のコードテンプレート入れていらっしゃる人でしたら、これ以降、入力は簡単です。活用していきます。

使いながら、アップデートしていくかも知れません。また、自分に合うように、コードテンプレートは変更し、活用しましょう。

SQLの実行

まず、SQLを直接実行する方法です。以降、DBのアクセスに利用するのはDBクラスです。（本来のFuelPHPのネーミング規則に従えば、Dbクラスのはずなんですけど、これだけ規則に外れています。）

SQLを直接実行するのはDB：：query（）です。新しいコントローラーを開き、最初に、次のように打ち込んでください。

$result = DB::query('');

NetBeansの機能を使ってコードを入れていきましょう。既に前回、認証で使用したusersテーブルがあります。これを使って練習です。

まずシングルコーテーションのペアの中にselectと打ち込み、スペースをひとつ開けましょう。続いてコントロールを押しながらスペースバーを叩きます。

NetBeansの起動後、データベースに接続していない場合、データベース接続を選択と表示されます。Enterキーで決定してください。

続いて、実際にどの接続設定を使用するかプルダウンから選びます。もし、前回作成したものがない場合、新規作成で作り直しましょう。手順は似たようなものです。

さて、接続されると補完候補が出てきます。よく見ると、オレンジがテーブル名、ブルーがDB名です。では、一回補完ウィンドウを閉じましょう。

selectに続き、スペースをひとつ開け、fromと打ち込み、スペースを開け、コントロール＋スペースを叩いてください。補完候補がでます。ここはテーブル名ですね。usersを選んでください。

ではカーソルをselectとformの間に戻してください。fromの前で結構です。そこで、コントロール＋スペースです。すると、usersの項目が表示されます。カンマで区切りながら、いくつか選びましょう。その中のgroupを選択すると、これはSQLのSELECT文の一部と同じ文字列ですから、自動的にエスケープされます。

項目を入れ終わったら、テーブル名の最後減移動してください。スペースを開け、コントロール＋スペースです。するとWHERE文が補完候補として、選択されています。この調子で、SQL文を入力補助を受けながら、打ち込めます。

SELECT文だけでなく、INSERT文でも、UPDATE文でも入力補完が働きます。NetBeansはフレームワークを利用せず、生でコード書くときに、本当に便利です。

SQL文を記述したら、その実行はexecute()文です。実際は続けてこう書くのが普通でしょう。

$result = DB::query('select from users')->execute();

SELECT文

私のコードテンプレートセットされた方は、dbselに続いてタブを叩いてください。ずらりと展開されます。（以下は、展開された文を整形したものです。コードの展開後はインデントなどが乱れますので、エラーがなければ、速攻整形しましょう。）

$result =
    DB::select('フィールド', array('フィールド', 'エイリアス'))
    ->from('table')
    ->join('pictures', 'RIGHT OUTER')
    ->on('users.id', '=', 'profiles.user_id')
    ->where_open()
    ->where('id', '=', 1)
    ->and_where('id', 'in', array(1, 2, 3))
    ->where_close()
    ->or_where_open()
    ->where('id', 'between', array(1, 10))
    ->or_where('name', 'like', 'joe')
    ->or_where_close()
    ->order_by('name', 'asc')
    ->order_by('surname', 'desc')
    ->group_by('genre', 'category')
    ->having('name', '!=', 'John')
    ->and_having('surname', '=', 'Doe')
    ->limit(10)
    ->offset(5)
    ->distinct(true)
    ->as_object()
    ->execute();

私は調べるのが面倒ですから、項目を並べるだけ並べて、いらないものを削除するのが好きです。ですから、使える関数全部つけになっています。

もし、あなたが効率を重視するなら、一番使用するSELECTのパターンを登録しましょう。

select()はセレクトする項目をカンマで区切って並べます。別名を設定したいときはarray('項目名', '別名')と記述します。

条件を絞り込むwhere系では、引数２つなら、等しいかの比較、３つなら、２つ目は比較演算子です。likeの使い方なども、展開されたコードでわかるようになっています。

distinct(true)で重複を取り除きます。

返される結果は連想配列です。foreachで回せます。配列でなくオブジェクトで受け取りたいときは、as_object()を付けます。

SELECT結果

結果は連想配列、またはオブジェクトですが、ビューに直接渡さないようにしましょう。今日もFuelPHPのフォーラムで外人さんが、この問題に引っかかっていました。ドキュメントでも分かりづらいのです。

返ってくる結果はプロテクトされており、書きこむとエラーになります。ビューではアウトプットはデフォルトでフィルターがかかります。このフィルターが変更しようしてエラーになります。

結果を組み替えたり、計算したりして、新しい変数、配列を作成するか、もしくは、$result->as_array()とすると、新たに連想配列が生成されます。この形でわたしましょう。

as_array()は項目名を引数に取り、引数一つなら、それをキーとして連想配列を作成します。二つなら、最初をキー、２つ目を値として連想配列が作成されます。

試してみてください。

INSERT文

dbinsに続いてタブで展開されます。コードテンプレートを登録していない人は、こちらをどうぞ。

list($insert_id, $影響を受けた行数) = DB::insert(table)
    ->set(array(
        'name'     => 'John Random',
        'email'    => '
 このメールアドレスは、スパムロボットから保護されています。アドレスを確認するにはJavaScriptを有効にしてください
 ',
        'password' => 's0_s3cr3t',
    ))
    ->execute();

リターンは配列で、最初がインサートされた場所、２つ目が影響を受けた行数です。項目の値は、キーと値のペアで指定します。

UPDATE文

dbupdに続いてタブで展開です。

$影響を受けた行数 = DB::update('table')
    ->value('対象フィールド１', $更新内容１)
    ->set(array(
        '対象フィールド２' => $更新内容２,
        '対象フィールド３' => $更新内容３,
    ))
    ->where_open()
    ->where('id', '=', 1)
    ->and_where('id', 'in', array(1, 2, 3))
    ->where_close()
    ->or_where_open()
    ->where('id', 'between', array(1, 10))
    ->or_where('name', 'like', 'joe')
    ->or_where_close()
    ->execute();

リターンが閉胸を受けた行数、変更内容の渡し方が２つあり、value()はフィールド名、内容をそのまま引数で取り、set()は配列で取ります。

DELETE文

dbdelで展開されます。

$影響を受けた行数 = DB::delete(table)
    ->where_open()
    ->where('id', '=', 1)
    ->and_where('id', 'in', array(1, 2, 3))
    ->where_close()
    ->or_where_open()
    ->where('id', 'between', array(1, 10))
    ->or_where('name', 'like', 'joe')
    ->or_where_close()
    ->execute();

条件の指定はSELECT文と同様ですね。

結果を一行だけ受け取る

テーブルに存在する項目に、特定の値が存在しているかどうかチェックしたい場合、その結果の一行だけ調べられると便利です。

その様な場合に利用できるテンプレートも登録してあります。dbcurです。

$result = DB::select('フィールド', array('フィールド', 'エイリアス'))
        ->from('table')
        ->join('pictures', 'RIGHT OUTER')
        ->on('users.id', '=', 'profiles.user_id')
        ->where_open()
        ->where('id', '=', 1)
        ->and_where('id', 'in', array(1, 2, 3))
        ->where_close()
        ->or_where_open()
        ->where('id', 'between', array(1, 10))
        ->or_where('name', 'like', 'joe')
        ->or_where_close()
        ->order_by('name', 'asc')
        ->order_by('surname', 'desc')
        ->group_by('genre', 'category')
        ->having('name', '!=', 'John')
        ->and_having('surname', '=', 'Doe')
        ->limit(10)
        ->offset(5)
        ->distinct(true)
        ->as_object()
        ->execute()->current();
$a = $result['参照フィールド'];

テンプレートに登録してあるコードは最初のselect()がシンタックスエラーになります。どうぞ、ご自身で修正して、ご利用ください。

通常のSELECTとの違いは、最後にcurrent()を実行していることです。これで戻り値はforeachで回さなくても、そのまま項目を指定すれば、参照可能です。存在チェックも$resultを調べるだけですみます。存在しない場合、$resultはnullになります。確かめてください。

後は、ドキュメントの翻訳が進むまで、待ちましょう。でも、普通に組むプログラムでしたら、この程度で大丈夫ですね。