イントロダクション
データベースクエリービルダーはスラスラと書ける(fluent)便利なインターフェイスで、クエリーを作成し実行するために使用します。アプリケーションで行われるほとんどのデーターベース操作が可能で、サポートしている全データベースシステムに対し使用できます。
注意: LaravelクエリービルダーはアプリケーションをSQLインジェクション攻撃から守るために、PDOパラメーターによるバインディングを使用します。バインドする文字列をクリーンにしてから渡す必要はありません。
結果の取得
全レコードの取得
クエリーをスラスラと書くにはDB
ファサードのtable
メソッドを使います。table
メソッドは指定したテーブルに対するクエリービルダーインスタンスを返します。これを使いクエリーに制約を加え、最終的な結果を取得するチェーンを繋げます。次の例でテーブルの全レコードを取得(get
)してみましょう。
<?php
namespace App\Http\Controllers;
use DB;
use App\Http\Controllers\Controller;
class UserController extends Controller
{
/**
* アプリケーションの全ユーザーレコード一覧を表示
*
* @return Response
*/
public function index()
{
$users = DB::table('users')->get();
return view('user.index', ['users' => $users]);
}
}
直接クエリーを行う場合と同様に、get
メソッドはPHPのStdClass
オブジェクトのインスタンスを結果とする「配列」を返します。オブジェクトのプロパティーによりカラムの値にアクセスできます。
foreach ($users as $user) {
echo $user->name;
}
テーブルから1カラム/1レコード取得
データベーステーブルから1レコードのみ取得する必要がある場合は、first
メソッドを使います。このメソッドはStdClass
オブジェクトを返します。
$user = DB::table('users')->where('name', 'John')->first();
echo $user->name;
全カラムは必要ない場合、value
メソッドにより一つの値のみ取得できます。このメソッドはカラムの値を直接返します。
$email = DB::table('users')->where('name', 'John')->value('email');
結果の分割
数千のデータベースレコードを扱う場合はchunk
メソッドの使用を考慮してください。このメソッドは一度に小さな「かたまり(chunk)」で結果を取得し、このチャンクは処理のために「クロージャ」に渡されます。このメソッドは数千のレコードを処理するArtisanコマンドを書くときに便利です。users
レコード全体から一度に100レコードずつチャンクを処理する例を見てください。
DB::table('users')->chunk(100, function($users) {
foreach ($users as $user) {
//
}
});
クロージャーからfalse
を返すとチャンクの処理を中断できます。
DB::table('users')->chunk(100, function($users) {
// レコードの処理…
return false;
});
カラム値をリストで取得
配列で一つのカラムの値を取得したい場合はlists
メソッドを使います。以下の例では役割名(title)を配列で取得しています。
$titles = DB::table('roles')->lists('title');
foreach ($titles as $title) {
echo $title;
}
取得配列のキーカラムを指定することもできます。
$roles = DB::table('roles')->lists('title', 'name');
foreach ($roles as $name => $title) {
echo $title;
}
集計
またクエリービルダーはcount
、max
、min
、avg
、sum
など多くの集計メソッドを提供しています。クエリーを制約した後にこれらのメソッドを使うことも可能です。
$users = DB::table('users')->count();
$price = DB::table('orders')->max('price');
もちろんこれらのメソッドをクエリーを構築するために他の節と組み合わせて使用できます。
$price = DB::table('orders')
->where('finalized', 1)
->avg('price');
SELECT
SELECT節の指定
もちろん、いつもデータベースレコードの全カラムが必要ではないでしょう。クエリーのselect
節をselect
メソッドで指定できます。
$users = DB::table('users')->select('name', 'email as user_email')->get();
distinct
メソッドで重複行をまとめた結果を返すように強制できます。
$users = DB::table('users')->distinct()->get();
既にクエリービルダーインスタンスがあり、select節にカラムを追加したい場合はaddSelect
メソッドを使ってください。
$query = DB::table('users')->select('name');
$users = $query->addSelect('age')->get();
SQL文の直接指定
たまにクエリーの中でSQLを直接使用したいことがあります。このようなSQLでは文字をそのまま埋め込むだけですので、SQLインジェクションをされないように気をつけてください! エスケープなしのSQLを使用する場合はDB::raw
メソッドを使用します。
$users = DB::table('users')
->select(DB::raw('count(*) as user_count, status'))
->where('status', '<>', 1)
->groupBy('status')
->get();
JOIN
INNER JOIN文
さらにクエリービルダーはJOIN文を書くためにも使用できます。基本的な"INNER
JOIN"を実行するには、クエリービルダーインスタンスにjoin
メソッドを使ってください。join
メソッドの第1引数は結合したいテーブル名、それ以降の引数にはJOIN時のカラムの制約条件を指定します。もちろん下記のように、一つのクエリーで複数のテーブルを結合できます。
$users = DB::table('users')
->join('contacts', 'users.id', '=', 'contacts.user_id')
->join('orders', 'users.id', '=', 'orders.user_id')
->select('users.*', 'contacts.phone', 'orders.price')
->get();
LEFT JOIN文
"INNER JOIN"の代わりに"LEFT
JOIN"を実行したい場合はleftJoin
メソッドを使います。leftJoin
メソッドの使い方はjoin
メソッドと同じです。
$users = DB::table('users')
->leftJoin('posts', 'users.id', '=', 'posts.user_id')
->get();
上級のJOIN文
さらに上級なJOIN節を指定することもできます。そのためにはjoin
メソッドの第2引数に「クロージャー」を指定します。その「クロージャー」はJOIN
節に制約を指定できるようにするJoinClause
オブジェクトを受け取ります。
DB::table('users')
->join('contacts', function ($join) {
$join->on('users.id', '=', 'contacts.user_id')->orOn(...);
})
->get();
JOINに"where"節を使用したい場合はjoinの中でwhere
やorWhere
を使用して下さい。2つのカラムを比べる代わりに、これらのメソッドは値とカラムを比較します。
DB::table('users')
->join('contacts', function ($join) {
$join->on('users.id', '=', 'contacts.user_id')
->where('contacts.user_id', '>', 5);
})
->get();
UNION
クエリービルダーは2つのクエリーを結合(union)させる手軽な手法を提供します。たとえば最初にクエリーを作成し、その後に2つ目のクエリーを結合するためにunion
メソッドを使えます。
$first = DB::table('users')
->whereNull('first_name');
$users = DB::table('users')
->whereNull('last_name')
->union($first)
->get();
同じ使い方のunionAll
メソッドも使えます。
WHERE節
単純なWHERE節
where
節をクエリーに追加するには、クエリービルダーインスタンスのwhere
メソッドを使います。基本的なwhere
の呼び出しでは3つの引数を使います。第1引数はカラム名です。第2引数はデータベースがサポートしているオペレーターです。第3引数はカラムに対して比較する値です。
例として、"votes"カラムの値が100と等しいレコードのクエリーを見てください。
$users = DB::table('users')->where('votes', '=', 100)->get();
利便性のため、カラムが指定値と等しいかを比べたい場合は、where
メソッドの第2引数に値をそのまま指定できます。
$users = DB::table('users')->where('votes', 100)->get();
もちろん、where
文を書くときにその他のいろいろなオペレータも使えます。
$users = DB::table('users')
->where('votes', '>=', 100)
->get();
$users = DB::table('users')
->where('votes', '<>', 100)
->get();
$users = DB::table('users')
->where('name', 'like', 'T%')
->get();
OR節
WHEREの結合にチェーンでor
節をクエリーに追加できます。orWhere
メソッドはwhere
メソッドと同じ引数を受け付けます。
$users = DB::table('users')
->where('votes', '>', 100)
->orWhere('name', 'John')
->get();
その他のWHERE節
whereBetween
whereBetween
メソッドはカラムの値が2つの値の間である条件を加えます。
$users = DB::table('users')
->whereBetween('votes', [1, 100])->get();
whereNotBetween
whereNotBetween
メソッドは、カラムの値が2つの値の間ではない条件を加えます。
$users = DB::table('users')
->whereNotBetween('votes', [1, 100])
->get();
whereIn / whereNotIn
whereIn
メソッドは指定した配列の中にカラムの値が含まれている条件を加えます。
$users = DB::table('users')
->whereIn('id', [1, 2, 3])
->get();
whereNotIn
メソッドはカラムの値が指定した配列の中に含まれていない条件を加えます。
$users = DB::table('users')
->whereNotIn('id', [1, 2, 3])
->get();
whereNull / whereNotNull
whereNull
メソッドは指定したカラムの値がNULL
である条件を加えます。
$users = DB::table('users')
->whereNull('updated_at')
->get();
whereNotBetween
メソッドは指定したカラムの値がNULL
でない条件を加えます。
$users = DB::table('users')
->whereNotNull('updated_at')
->get();
高度なWhere節
パラメーターのグループ化
時には"WHERE EXISTS"やグループにまとめたパラーメーターのネストのような、上級のWHERE節を作成する必要が起きます。Laravelクエリービルダーはこれらもうまく処理できます。手始めにカッコで制約をまとめる例を見てください。
DB::table('users')
->where('name', '=', 'John')
->orWhere(function ($query) {
$query->where('votes', '>', 100)
->where('title', '<>', 'Admin');
})
->get();
ご覧の通り、orWhere
メソッドに渡している「クロージャー」が、クエリービルダーのグルーピングを指示しています。生成するSQLの括弧内で展開される制約を指定できるように、「クロージャー」はクエリービルダーのインスタンスを受け取ります。
select * from users where name = 'John' or (votes > 100 and title <> 'Admin')
Exist節
whereExists
メソッドはWHERE EXIST
のSQLを書けるように用意しています。whereExists
メソッドは引数に「クロージャー」を取り、"EXISTS"節の中に置かれるクエリーを定義するためのクエリービルダーを受け取ります。
DB::table('users')
->whereExists(function ($query) {
$query->select(DB::raw(1))
->from('orders')
->whereRaw('orders.user_id = users.id');
})
->get();
上のクエリーは以下のSQLを生成します。
select * from users
where exists (
select 1 from orders where orders.user_id = users.id
)
順序、グループ分け、制限、オフセット
orderBy
orderBy
メソッドは指定したカラムでクエリー結果をソートします。orderBy
メソッドの最初の引数はソート対象のカラムで、第2引数はソートの昇順(asc
)と降順(desc
)をコントロールします。
$users = DB::table('users')
->orderBy('name', 'desc')
->get();
groupBy / having / havingRaw
groupBy
とhaving
メソッドはクエリー結果をグループにまとめるために使用します。having
メソッドはwhere
メソッドと似た使い方です。
$users = DB::table('users')
->groupBy('account_id')
->having('account_id', '>', 100)
->get();
havingRaw
メソッドはhaveing
節の値としてSQL文字列をそのまま指定するために使用します。たとえば2,500ドルより多く売り上げている部門(department)を全部見つけましょう。
$users = DB::table('orders')
->select('department', DB::raw('SUM(price) as total_sales'))
->groupBy('department')
->havingRaw('SUM(price) > 2500')
->get();
skip / take
クエリーから限られた数の(LIMIT
)レコードを受け取ったり、結果から指定した件数を飛ばしたり(OFFSET
)するには、skip
とtake
メソッドを使います。
$users = DB::table('users')->skip(10)->take(5)->get();
INSERT
クエリービルダーは、データベーステーブルにレコードを挿入するためのinsert
メソッドを提供しています。insert
メソッドは挿入するカラム名と値の配列を引数に取ります。
DB::table('users')->insert(
['email' => 'john@example.com', 'votes' => 0]
);
配列の配列をinsert
に渡して呼び出すことで、テーブルにたくさんのレコードを一度にまとめて挿入できます。
DB::table('users')->insert([
['email' => 'taylor@example.com', 'votes' => 0],
['email' => 'dayle@example.com', 'votes' => 0]
]);
自動増分ID
テーブルが自動増分IDを持っている場合、insertGetId
メソッドを使うとレコードを挿入し、そのレコードのIDを返してくれます。
$id = DB::table('users')->insertGetId(
['email' => 'john@example.com', 'votes' => 0]
);
注意: PostgreSQLで
insertGetId
メソッドを使う場合、自動増分カラム名はid
である必要があります。他の「シーケンス」からIDを取得したい場合は、insertGetId
メソッドの第2引数にシーケンス名を指定してください。
UPDATE
もちろん、データベースへレコードを挿入するだけでなく、存在しているレコードをupdate
メソッドで更新することもできます。update
メソッドはinsert
メソッドと同様に、更新対象のカラムのカラム名と値の配列を引数に受け取ります。更新するクエリーをwhere
節を使って制約することもできます。
DB::table('users')
->where('id', 1)
->update(['votes' => 1]);
カラム値の増加/減少
クエリービルダーは指定したカラムの値を増やしたり、減らしたりするのに便利なメソッドも用意しています。これはシンプルな短縮記法で、update
分で書くのに比べるとより記述的であり、簡潔なインターフェイスを提供しています。
両方のメソッドともに変更したいカラムを第1引数に指定します。オプションの第2引数はそのカラムの増減値を指定します。
DB::table('users')->increment('votes');
DB::table('users')->increment('votes', 5);
DB::table('users')->decrement('votes');
DB::table('users')->decrement('votes', 5);
さらに増減操作と一緒に更新する追加のカラムを指定することもできます。
DB::table('users')->increment('votes', 1, ['name' => 'John']);
DELETE
もちろん、クエリービルダーはdelete
メソッドで、テーブルからレコードを削除するためにも使用できます。
DB::table('users')->delete();
delete
メソッドを呼び出す前にwhere
節を追加し、delete
文を制約することもできます。
DB::table('users')->where('votes', '<', 100)->delete();
全レコードを削除し、自動増分のIDを0にリセットするためにテーブルをTRUNCATEしたい場合は、truncate
メソッドを使います。
DB::table('users')->truncate();
悲観的ロック
クエリービルダーは、SELECT
文で「悲観的ロック」を行うための機能をいくつか持っています。SELECT文を実行する間「共有ロック」をかけたい場合は、sharedLock
メソッドをクエリーに指定して下さい。共有ロックはトランザクションがコミットされるまで、SELECTしている行が更新されることを防ぎます。
DB::table('users')->where('votes', '>', 100)->sharedLock()->get();
もしくはlockForUpdate
メソッドが使えます。占有ロックをかけることで、レコードを更新したりSELECTするために他の共有ロックが行われるのを防ぎます。
DB::table('users')->where('votes', '>', 100)->lockForUpdate()->get();