Laravel 5.0 クエリービルダー

イントロダクションIntroduction

データベースクエリービルダーはスラスラと書ける(fluent)便利なインターフェイスで、クエリーを作成し実行するために使用します。アプリケーションで行われるほとんどのデーターベース操作が可能で、サポートしている全データベースシステムに対し使用できます。The database query builder provides a convenient, fluent interface to creating and running database queries. It can be used to perform most database operations in your application, and works on all supported database systems.

注意： Laravelクエリービルダーは、アプリケーションをSQLインジェクション攻撃から守るために、PDOパラメーターによるバインディングを使用します。バインドする文字列をクリーンにしてから渡す必要はありません。Note: The Laravel query builder uses PDO parameter binding throughout to protect your application against SQL injection attacks. There is no need to clean strings being passed as bindings.

SELECTSelects

全レコードの取得Retrieving All Rows From A Table

$users = DB::table('users')->get();

foreach ($users as $user)
{
	var_dump($user->name);
}

結果の分割Chunking Results From A Table

DB::table('users')->chunk(100, function($users)
{
	foreach ($users as $user)
	{
		//
	}
});

クロージャーからfalseを返すことにより、分割処理を中断できます。You may stop further chunks from being processed by returning false from the Closure:

DB::table('users')->chunk(100, function($users)
{
	//

	return false;
});

テーブルから１レコード取得Retrieving A Single Row From A Table

$user = DB::table('users')->where('name', 'John')->first();

var_dump($user->name);

レコードの１カラム取得Retrieving A Single Column From A Row

$name = DB::table('users')->where('name', 'John')->pluck('name');

カラム値をリストで取得Retrieving A List Of Column Values

$roles = DB::table('roles')->lists('title');

このメソッド使用例ではroleのタイトルの配列がリターンされます。返される配列のキーカラムを指定することもできます。This method will return an array of role titles. You may also specify a custom key column for the returned array:

$roles = DB::table('roles')->lists('title', 'name');

SELECT節の指定Specifying A Select Clause

$users = DB::table('users')->select('name', 'email')->get();

$users = DB::table('users')->distinct()->get();

$users = DB::table('users')->select('name as user_name')->get();

存在するクエリーにSELECT節を追加Adding A Select Clause To An Existing Query

$query = DB::table('users')->select('name');

$users = $query->addSelect('age')->get();

WHERE節の指定Using Where Operators

$users = DB::table('users')->where('votes', '>', 100)->get();

OR文Or Statements

$users = DB::table('users')
                    ->where('votes', '>', 100)
                    ->orWhere('name', 'John')
                    ->get();

WHERE BETWEENUsing Where Between

$users = DB::table('users')
                    ->whereBetween('votes', [1, 100])->get();

WHERE NOT BETWEENの使用Using Where Not Between

$users = DB::table('users')
                    ->whereNotBetween('votes', [1, 100])->get();

配列でWHERE INを指定Using Where In With An Array

$users = DB::table('users')
                    ->whereIn('id', [1, 2, 3])->get();

$users = DB::table('users')
                    ->whereNotIn('id', [1, 2, 3])->get();

値が未設定のレコードを見つけるためにWHERE NULLを使用Using Where Null To Find Records With Unset Values

$users = DB::table('users')
                    ->whereNull('updated_at')->get();

動的Where節Dynamic Where Clauses

楽にwhere文を記述するため、magicメソッドを利用した「動的」なwhere文を使うこともできます。You may even use "dynamic" where statements to fluently build where statements using magic methods:

$admin = DB::table('users')->whereId(1)->first();

$john = DB::table('users')
                    ->whereIdAndEmail(2, 'john@doe.com')
                    ->first();

$jane = DB::table('users')
                    ->whereNameOrAge('Jane', 22)
                    ->first();

ORDER BY、GROUP BY、HAVINGOrder By, Group By, And Having

$users = DB::table('users')
                    ->orderBy('name', 'desc')
                    ->groupBy('count')
                    ->having('count', '>', 100)
                    ->get();

OFFSETとLIMITOffset & Limit

$users = DB::table('users')->skip(10)->take(5)->get();

JOINJoins

クエリービルダーはJOIN文を書くためにも使用できます。以下の例をご覧ください。The query builder may also be used to write join statements. Take a look at the following examples:

基本的なJOIN文Basic Join Statement

DB::table('users')
            ->join('contacts', 'users.id', '=', 'contacts.user_id')
            ->join('orders', 'users.id', '=', 'orders.user_id')
            ->select('users.id', 'contacts.phone', 'orders.price')
            ->get();

LEFT JOIN文Left Join Statement

DB::table('users')
	    ->leftJoin('posts', 'users.id', '=', 'posts.user_id')
	    ->get();

もっと複雑なJOIN節も指定できます。You may also specify more advanced join clauses:

DB::table('users')
        ->join('contacts', function($join)
        {
        	$join->on('users.id', '=', 'contacts.user_id')->orOn(...);
        })
        ->get();

JOINに"where"節を使用したい場合は、joinの中でwhereやorWhereを使用して下さい。２つのカラムを比べる代わりに、これらのメソッドは値に対してカラムを比較します。If you would like to use a "where" style clause on your joins, you may use the where and orWhere methods on a join. Instead of comparing two columns, these methods will compare the column against a value:

DB::table('users')
        ->join('contacts', function($join)
        {
        	$join->on('users.id', '=', 'contacts.user_id')
        	     ->where('contacts.user_id', '>', 5);
        })
        ->get();

WHEREAdvanced Wheres

パラメーターのグループ化Parameter Grouping

時々、"where exists"とかネストしたグループ分けのような、更に上級なSQL文を生成する必要があるでしょう。Laravelのクエリービルダーで上手く処理できます。Sometimes you may need to create more advanced where clauses such as "where exists" or nested parameter groupings. The Laravel query builder can handle these as well:

DB::table('users')
            ->where('name', '=', 'John')
            ->orWhere(function($query)
            {
            	$query->where('votes', '>', 100)
            	      ->where('title', '<>', 'Admin');
            })
            ->get();

クエリービルダーは以下のSQLを作成します。The query above will produce the following SQL:

select * from users where name = 'John' or (votes > 100 and title <> 'Admin')

EXISTS文Exists Statements

DB::table('users')
            ->whereExists(function($query)
            {
            	$query->select(DB::raw(1))
            	      ->from('orders')
            	      ->whereRaw('orders.user_id = users.id');
            })
            ->get();

クエリービルダーは以下のSQLを作成します。The query above will produce the following SQL:

select * from users
where exists (
	select 1 from orders where orders.user_id = users.id
)

集計Aggregates

また、クエリービルダーはcount、max、min、avg、sumなど多くの集計メソッドを提供しています。The query builder also provides a variety of aggregate methods, such as count, max, min, avg, and sum.

集計メソッドの使用Using Aggregate Methods

$users = DB::table('users')->count();

$price = DB::table('orders')->max('price');

$price = DB::table('orders')->min('price');

$price = DB::table('orders')->avg('price');

$total = DB::table('users')->sum('votes');

SQL文の直接指定Raw Expressions

たまにクエリーの中でSQLを直接使用したいことがあります。このようなSQLでは、文字をそのまま埋め込むだけですので、SQLインジェクションをされないように気をつけてください！エスケープなしのSQLを使用する場合はDB::rawメソッドを使用します。Sometimes you may need to use a raw expression in a query. These expressions will be injected into the query as strings, so be careful not to create any SQL injection points! To create a raw expression, you may use the DB::raw method:

SQLを直接使用Using A Raw Expression

$users = DB::table('users')
                     ->select(DB::raw('count(*) as user_count, status'))
                     ->where('status', '<>', 1)
                     ->groupBy('status')
                     ->get();

INSERTInserts

レコードの挿入Inserting Records Into A Table

DB::table('users')->insert(
	['email' => 'john@example.com', 'votes' => 0]
);

レコードを挿入し、自動増加したIDを取得Inserting Records Into A Table With An Auto-Incrementing ID

テーブルに自動増分IDがある場合はinsertGetIdを使い、レコードを挿入し、そのIDを取得してください。If the table has an auto-incrementing id, use insertGetId to insert a record and retrieve the id:

$id = DB::table('users')->insertGetId(
	['email' => 'john@example.com', 'votes' => 0]
);

注目：　PostgreSQLにinsertGetIdメソッドを使用するときは、自動増分カラムの名前を"id"にしてください。Note: When using PostgreSQL the insertGetId method expects the auto-incrementing column to be named "id".

複数レコードの挿入Inserting Multiple Records Into A Table

DB::table('users')->insert([
	['email' => 'taylor@example.com', 'votes' => 0],
	['email' => 'dayle@example.com', 'votes' => 0]
]);

UPDATEUpdates

レコード更新Updating Records In A Table

DB::table('users')
            ->where('id', 1)
            ->update(['votes' => 1]);

カラム値の増加／減少Incrementing or decrementing a value of a column

DB::table('users')->increment('votes');

DB::table('users')->increment('votes', 5);

DB::table('users')->decrement('votes');

DB::table('users')->decrement('votes', 5);

更に更新する追加のカラムも指定できます。You may also specify additional columns to update:

DB::table('users')->increment('votes', 1, ['name' => 'John']);

DELETEDeletes

レコード削除Deleting Records In A Table

DB::table('users')->where('votes', '<', 100)->delete();

全レコード削除Deleting All Records From A Table

DB::table('users')->delete();

テーブルのTRUNCATETruncating A Table

DB::table('users')->truncate();

クエリー結合Unions

クエリービルダーは２つのクエリーを結合(union)させる手軽な手法を提供します。The query builder also provides a quick way to "union" two queries together:

$first = DB::table('users')->whereNull('first_name');

$users = DB::table('users')->whereNull('last_name')->union($first)->get();

unionAllメソッドも使用でき、unionと同じ使い方をします。The unionAll method is also available, and has the same method signature as union.

排他的ロックPessimistic Locking

クエリービルダーは、SELECT文で排他的ロックを行うための機能をいくつか持っています。The query builder includes a few functions to help you do "pessimistic locking" on your SELECT statements.

「共有ロック」でSELECT文を実行する場合は、sharedLockメソッドをクエリーで指定して下さい。To run the SELECT statement with a "shared lock", you may use the sharedLock method on a query:

DB::table('users')->where('votes', '>', 100)->sharedLock()->get();

SELECT文を「専有ロック」で実行したい場合は、lockForUpdateメソッドをクエリーに使用します。To "lock for update" on a SELECT statement, you may use the lockForUpdate method on a query:

DB::table('users')->where('votes', '>', 100)->lockForUpdate()->get();