Laravel 5.1 データベース：クエリービルダー

イントロダクション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 to protect your application against SQL injection attacks. There is no need to clean strings being passed as bindings.

結果の取得Retrieving Results

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

クエリーをスラスラと書くにはDBファサードのtableメソッドを使います。tableメソッドは指定したテーブルに対するクエリービルダーインスタンスを返します。これを使いクエリーに制約を加え、最終的な結果を取得するチェーンを繋げます。次の例でテーブルの全レコードを取得(get)してみましょう。To begin a fluent query, use the table method on the DB facade. The table method returns a fluent query builder instance for the given table, allowing you to chain more constraints onto the query and then finally get the results. In this example, let's just get all records from a table:

<?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オブジェクトのインスタンスを結果とする「配列」を返します。オブジェクトのプロパティーによりカラムの値にアクセスできます。Like raw queries[/docs/{{version}}/database], the get method returns an array of results where each result is an instance of the PHP StdClass object. You may access each column's value by accessing the column as a property of the object:

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

テーブルから１カラム／１レコード取得Retrieving A Single Row / Column From A Table

データベーステーブルから１レコードのみ取得する必要がある場合は、firstメソッドを使います。このメソッドはStdClassオブジェクトを返します。If you just need to retrieve a single row from the database table, you may use the first method. This method will return a single StdClass object:

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

echo $user->name;

全カラムは必要ない場合、valueメソッドにより一つの値のみ取得できます。このメソッドはカラムの値を直接返します。If you don't even need an entire row, you may extract a single value from a record using the value method. This method will return the value of the column directly:

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

結果の分割Chunking Results From A Table

数千のデータベースレコードを扱う場合はchunkメソッドの使用を考慮してください。このメソッドは一度に小さな「かたまり(chunk)」で結果を取得し、このチャンクは処理のために「クロージャ」に渡されます。このメソッドは数千のレコードを処理するArtisanコマンドを書くときに便利です。usersレコード全体から一度に１００レコードずつチャンクを処理する例を見てください。If you need to work with thousands of database records, consider using the chunk method. This method retrieves a small "chunk" of the results at a time, and feeds each chunk into a Closure for processing. This method is very useful for writing Artisan commands[/docs/{{version}}/artisan] that process thousands of records. For example, let's work with the entire users table in chunks of 100 records at a time:

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 List Of Column Values

配列で一つのカラムの値を取得したい場合はlistsメソッドを使います。以下の例では役割名(title)を配列で取得しています。If you would like to retrieve an array containing the values of a single column, you may use the lists method. In this example, we'll retrieve an array of role titles:

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

foreach ($titles as $title) {
    echo $title;
}

取得配列のキーカラムを指定することもできます。You may also specify a custom key column for the returned array:

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

foreach ($roles as $name => $title) {
    echo $title;
}

集計Aggregates

またクエリービルダーはcount、max、min、avg、sumなど多くの集計メソッドを提供しています。クエリーを制約した後にこれらのメソッドを使うことも可能です。The query builder also provides a variety of aggregate methods, such as count, max, min, avg, and sum. You may call any of these methods after constructing your query:

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

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

もちろんこれらのメソッドをクエリーを構築するために他の節と組み合わせて使用できます。Of course, you may combine these methods with other clauses to build your query:

$price = DB::table('orders')
                ->where('finalized', 1)
                ->avg('price');

SELECTSelects

SELECT節の指定Specifying A Select Clause

もちろん、いつもデータベースレコードの全カラムが必要ではないでしょう。クエリーのselect節をselectメソッドで指定できます。Of course, you may not always want to select all columns from a database table. Using the select method, you can specify a custom select clause for the query:

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

distinctメソッドで重複行をまとめた結果を返すように強制できます。The distinct method allows you to force the query to return distinct results:

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

既にクエリービルダーインスタンスがあり、select節にカラムを追加したい場合はaddSelectメソッドを使ってください。If you already have a query builder instance and you wish to add a column to its existing select clause, you may use the addSelect method:

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

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

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:

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

JOINJoins

INNER JOIN文Inner Join Statement

さらにクエリービルダーはJOIN文を書くためにも使用できます。基本的な"INNER JOIN"を実行するには、クエリービルダーインスタンスにjoinメソッドを使ってください。joinメソッドの第１引数は結合したいテーブル名、それ以降の引数にはJOIN時のカラムの制約条件を指定します。もちろん下記のように、一つのクエリーで複数のテーブルを結合できます。The query builder may also be used to write join statements. To perform a basic SQL "inner join", you may use the join method on a query builder instance. The first argument passed to the join method is the name of the table you need to join to, while the remaining arguments specify the column constraints for the join. Of course, as you can see, you can join to multiple tables in a single query:

$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文Left Join Statement

"INNER JOIN"の代わりに"LEFT JOIN"を実行したい場合はleftJoinメソッドを使います。leftJoinメソッドの使い方はjoinメソッドと同じです。If you would like to perform a "left join" instead of an "inner join", use the leftJoin method. The leftJoin method has the same signature as the join method:

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

上級のJOIN文Advanced Join Statements

さらに上級なJOIN節を指定することもできます。そのためにはjoinメソッドの第２引数に「クロージャー」を指定します。その「クロージャー」はJOIN節に制約を指定できるようにするJoinClauseオブジェクトを受け取ります。You may also specify more advanced join clauses. To get started, pass a Closure as the second argument into the join method. The Closure will receive a JoinClause object which allows you to specify constraints on the join clause:

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();

UNIONUnions

クエリービルダーは２つのクエリーを結合(union)させる手軽な手法を提供します。たとえば最初にクエリーを作成し、その後に２つ目のクエリーを結合するためにunionメソッドを使えます。The query builder also provides a quick way to "union" two queries together. For example, you may create an initial query, and then use the union method to union it with a second query:

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

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

同じ使い方のunionAllメソッドも使えます。The unionAll method is also available and has the same method signature as union.

WHERE節Where Clauses

単純なWHERE節Simple Where Clauses

where節をクエリーに追加するには、クエリービルダーインスタンスのwhereメソッドを使います。基本的なwhereの呼び出しでは３つの引数を使います。第１引数はカラム名です。第２引数はデータベースがサポートしているオペレーターです。第３引数はカラムに対して比較する値です。To add where clauses to the query, use the where method on a query builder instance. The most basic call to where requires three arguments. The first argument is the name of the column. The second argument is an operator, which can be any of the database's supported operators. The third argument is the value to evaluate against the column.

例として、"votes"カラムの値が100と等しいレコードのクエリーを見てください。For example, here is a query that verifies the value of the "votes" column is equal to 100:

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

利便性のため、カラムが指定値と等しいかを比べたい場合は、whereメソッドの第２引数に値をそのまま指定できます。For convenience, if you simply want to verify that a column is equal to a given value, you may pass the value directly as the second argument to the where method:

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

もちろん、where文を書くときにその他のいろいろなオペレータも使えます。Of course, you may use a variety of other operators when writing a where clause:

$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節Or Statements

WHEREの結合にチェーンでor節をクエリーに追加できます。orWhereメソッドはwhereメソッドと同じ引数を受け付けます。You may chain where constraints together, as well as add or clauses to the query. The orWhere method accepts the same arguments as the where method:

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

その他のWHERE節Additional Where Clauses

whereBetweenwhereBetween

whereBetweenメソッドはカラムの値が２つの値の間である条件を加えます。The whereBetween method verifies that a column's value is between two values:

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

whereNotBetweenwhereNotBetween

whereNotBetweenメソッドは、カラムの値が２つの値の間ではない条件を加えます。The whereNotBetween method verifies that a column's value lies outside of two values:

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

whereIn / whereNotInwhereIn / whereNotIn

whereInメソッドは指定した配列の中にカラムの値が含まれている条件を加えます。The whereIn method verifies that a given column's value is contained within the given array:

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

whereNotInメソッドはカラムの値が指定した配列の中に含まれていない条件を加えます。The whereNotIn method verifies that the given column's value is not contained in the given array:

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

whereNull / whereNotNullwhereNull / whereNotNull

whereNullメソッドは指定したカラムの値がNULLである条件を加えます。The whereNull method verifies that the value of the given column is NULL:

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

whereNotBetweenメソッドは指定したカラムの値がNULLでない条件を加えます。The whereNotNull method verifies that the column's value is not NULL:

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

高度なWhere節Advanced Where Clauses

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

時には"WHERE EXISTS"やグループにまとめたパラーメーターのネストのような、上級のWHERE節を作成する必要が起きます。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. To get started, let's look at an example of grouping constraints within parenthesis:

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

ご覧の通り、orWhereメソッドに渡している「クロージャー」が、クエリービルダーのグルーピングを指示しています。生成するSQLの括弧内で展開される制約を指定できるように、「クロージャー」はクエリービルダーのインスタンスを受け取ります。As you can see, passing Closure into the orWhere method instructs the query builder to begin a constraint group. The Closure will receive a query builder instance which you can use to set the constraints that should be contained within the parenthesis group. The example above will produce the following SQL:

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

Exist節Exists Statements

whereExistsメソッドはWHERE EXISTのSQLを書けるように用意しています。whereExistsメソッドは引数に「クロージャー」を取り、"EXISTS"節の中に置かれるクエリーを定義するためのクエリービルダーを受け取ります。The whereExists method allows you to write where exist SQL clauses. The whereExists method accepts a Closure argument, which will receive a query builder instance allowing you to define the query that should be placed inside of the "exists" clause:

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
)

順序、グループ分け、制限、オフセットOrdering, Grouping, Limit, & Offset

orderByorderBy

orderByメソッドは指定したカラムでクエリー結果をソートします。orderByメソッドの最初の引数はソート対象のカラムで、第２引数はソートの昇順(asc)と降順(desc)をコントロールします。The orderBy method allows you to sort the result of the query by a given column. The first argument to the orderBy method should be the column you wish to sort by, while the second argument controls the direction of the sort and may be either asc or desc:

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

groupBy / having / havingRawgroupBy / having / havingRaw

groupByとhavingメソッドはクエリー結果をグループにまとめるために使用します。havingメソッドはwhereメソッドと似た使い方です。The groupBy and having methods may be used to group the query results. The having method's signature is similar to that of the where method:

$users = DB::table('users')
                ->groupBy('account_id')
                ->having('account_id', '>', 100)
                ->get();

havingRawメソッドはhaveing節の値としてSQL文字列をそのまま指定するために使用します。たとえば2,500ドルより多く売り上げている部門(department)を全部見つけましょう。The havingRaw method may be used to set a raw string as the value of the having clause. For example, we can find all of the departments with sales greater than $2,500:

$users = DB::table('orders')
                ->select('department', DB::raw('SUM(price) as total_sales'))
                ->groupBy('department')
                ->havingRaw('SUM(price) > 2500')
                ->get();

skip / takeskip / take

クエリーから限られた数の(LIMIT)レコードを受け取ったり、結果から指定した件数を飛ばしたり(OFFSET)するには、skipとtakeメソッドを使います。To limit the number of results returned from the query, or to skip a given number of results in the query (OFFSET), you may use the skip and take methods:

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

INSERTInserts

クエリービルダーは、データベーステーブルにレコードを挿入するためのinsertメソッドを提供しています。insertメソッドは挿入するカラム名と値の配列を引数に取ります。The query builder also provides an insert method for inserting records into the database table. The insert method accepts an array of column names and values to insert:

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

配列の配列をinsertに渡して呼び出すことで、テーブルにたくさんのレコードを一度にまとめて挿入できます。You may even insert several records into the table with a single call to insert by passing an array of arrays. Each array represents a row to be inserted into the table:

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

自動増分IDAuto-Incrementing IDs

テーブルが自動増分IDを持っている場合、insertGetIdメソッドを使うとレコードを挿入し、そのレコードのIDを返してくれます。If the table has an auto-incrementing id, use the insertGetId method to insert a record and then retrieve the ID:

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

注意： PostgreSQLでinsertGetIdメソッドを使う場合、自動増分カラム名はidである必要があります。他の「シーケンス」からIDを取得したい場合は、insertGetIdメソッドの第２引数にシーケンス名を指定してください。Note: When using PostgreSQL the insertGetId method expects the auto-incrementing column to be named id. If you would like to retrieve the ID from a different "sequence", you may pass the sequence name as the second parameter to the insertGetId method.

UPDATEUpdates

もちろん、データベースへレコードを挿入するだけでなく、存在しているレコードをupdateメソッドで更新することもできます。updateメソッドはinsertメソッドと同様に、更新対象のカラムのカラム名と値の配列を引数に受け取ります。更新するクエリーをwhere節を使って制約することもできます。Of course, in addition to inserting records into the database, the query builder can also update existing records using the update method. The update method, like the insert method, accepts an array of column and value pairs containing the columns to be updated. You may constrain the update query using where clauses:

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

カラム値の増加／減少Increment / Decrement

クエリービルダーは指定したカラムの値を増やしたり、減らしたりするのに便利なメソッドも用意しています。これはシンプルな短縮記法で、update分で書くのに比べるとより記述的であり、簡潔なインターフェイスを提供しています。The query builder also provides convenient methods for incrementing or decrementing the value of a given column. This is simply a short-cut, providing a more expressive and terse interface compared to manually writing the update statement.

両方のメソッドともに変更したいカラムを第１引数に指定します。オプションの第２引数はそのカラムの増減値を指定します。Both of these methods accept at least one argument: the column to modify. A second argument may optionally be passed to control the amount by which the column should be incremented / decremented.

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 during the operation:

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

DELETEDeletes

もちろん、クエリービルダーはdeleteメソッドで、テーブルからレコードを削除するためにも使用できます。Of course, the query builder may also be used to delete records from the table via the delete method:

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

deleteメソッドを呼び出す前にwhere節を追加し、delete文を制約することもできます。You may constrain delete statements by adding where clauses before calling the delete method:

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

全レコードを削除し、自動増分のIDを0にリセットするためにテーブルをTRUNCATEしたい場合は、truncateメソッドを使います。If you wish to truncate the entire table, which will remove all rows and reset the auto-incrementing ID to zero, you may use the truncate method:

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

悲観的ロックPessimistic Locking

クエリービルダーは、SELECT文で「悲観的ロック」を行うための機能をいくつか持っています。SELECT文を実行する間「共有ロック」をかけたい場合は、sharedLockメソッドをクエリーに指定して下さい。共有ロックはトランザクションがコミットされるまで、SELECTしている行が更新されることを防ぎます。The query builder also includes a few functions to help you do "pessimistic locking" on your select statements. To run the statement with a "shared lock", you may use the sharedLock method on a query. A shared lock prevents the selected rows from being modified until your transaction commits:

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

もしくはlockForUpdateメソッドが使えます。占有ロックをかけることで、レコードを更新したりSELECTするために他の共有ロックが行われるのを防ぎます。Alternatively, you may use the lockForUpdate method. A "for update" lock prevents the rows from being modified or from being selected with another shared lock:

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