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

イントロダクション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')->orderBy('id')->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')->orderBy('id')->chunk(100, function($users) {
    // Process the records...

    return false;
});

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

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

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

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

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

$roles = DB::table('roles')->pluck('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();

クロスジョイン文Cross Join Statement

「クロスジョイン」を実行するときは、接合したいテーブル名を指定し、crossJoinメソッドを使ってください。クロスジョインにより、最初のテーブルと指定したテーブルとの、デカルト積を生成します。To perform a "cross join" use the crossJoin method with the name of the table you wish to cross join to. Cross joins generate a cartesian product between the first table and the joined table:

$users = DB::table('sizes')
            ->crossJoin('colours')
            ->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();

whereに配列で条件を渡すこともできます。You may also pass an array of conditions to the where function:

$users = DB::table('users')->where([
    ['status', '=', '1'],
    ['subscribed', '<>', '1'],
])->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();

whereColumnwhereColumn

whereColumnメソッドは２つのカラムが同値である確認をするのに使います。The whereColumn method may be used to verify that two columns are equal:

$users = DB::table('users')
                ->whereColumn('first_name', 'last_name');

メソッドに比較演算子を追加指定することもできます。You may also pass a comparison operator to the method:

$users = DB::table('users')
                ->whereColumn('updated_at', '>', 'created_at');

whereColumnへ配列により複数の条件を渡すこともできます。各条件はandオペレータでつなげられます。The whereColumn method can also be passed an array of multiple conditions. These conditions will be joined using the and operator:

$users = DB::table('users')
                ->whereColumn([
                    ['first_name', 'last_name'],
                    ['updated_at', '>', 'created_at']
                ]);

高度な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 a 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 EXISTSのSQLを書けるように用意しています。whereExistsメソッドは引数に「クロージャー」を取り、"EXISTS"節の中に置かれるクエリーを定義するためのクエリービルダーを受け取ります。The whereExists method allows you to write where exists 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
)

JSON WHERE節JSON Where Clauses

Laravelはデータベース上のJSONタイプをサポートするカラムに対するクエリに対応しています。現在、MySQL5.7とPostgresに対応しています。JSONカラムをクエリーするには->オペレータを使ってください。Laravel supports querying JSON column types on databases that provide support for JSON column types. Currently, this includes MySQL 5.7 and Postgres. To query a JSON column, use the -> operator:

$users = DB::table('users')
                ->where('options->language', 'en')
                ->get();

$users = DB::table('users')
                ->where('preferences->dining->meal', 'salad')
                ->get();

順序、グループ分け、制限、オフセット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();

inRandomOrderinRandomOrder

inRandomOrderメソッドはクエリ結果をランダムな順番にしたい時に使用します。たとえば、以下のコードはランダムにユーザを一人取得します。The inRandomOrder method may be used to sort the query results randomly. For example, you may use this method to fetch a random user:

$randomUser = DB::table('users')
                ->inRandomOrder()
                ->first();

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

条件による制約Conditional Statements

ある条件がtrueの場合の時のみ、クエリへ特定の文を適用したい場合があります。例えば特定の入力値がリクエストに含まれている場合に、where文を適用する場合です。whenメソッドで実現できます。Sometimes you may want statements to apply to a query only when something else is true. For instance you may only want to apply a where statement if a given input value is present on the incoming request. You may accomplish this using the when method:

$role = $request->input('role');

$users = DB::table('users')
                ->when($role, function ($query) use ($role) {
                    return $query->where('role_id', $role);
                })
                ->get();

whenメソッドは、第１引数がtrueの時のみ、指定されたクロージャを実行します。最初の引数がfalseの場合、クロージャを実行しません。The when method only executes the given Closure when the first parameter is true. If the first parameter is false, the Closure will not be executed.

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