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

• イントロダクション
• 結果の取得
  • 結果の分割
  • 集計
• SELECT
• SQL文
• JOIN
• UNION
• WHERE節
  • パラメータのグループ分け
  • Where Exists節
  • JSON Where節
• 順序、グループ分け、制限、オフセット
• 条件節
• INSERT
• UPDATE
  • JSONカラムの更新
  • 増減分
• DELETE
• 排他的ロック

イントロダクション

データベースクエリビルダはスラスラと書ける(fluent)便利なインターフェイスで、クエリを作成し実行するために使用します。アプリケーションで行われるほとんどのデーターベース操作が可能で、サポートしている全データベースシステムに対し使用できます。

LaravelクエリビルダはアプリケーションをSQLインジェクション攻撃から守るために、PDOパラメーターによるバインディングを使用します。バインドする文字列をクリーンにしてから渡す必要はありません。

結果の取得

全レコードの取得

クエリを書くにはDBファサードのtableメソッドを使います。tableメソッドは指定したテーブルに対するクエリビルダインスタンスを返します。これを使いクエリに制約を加え、最終的な結果を取得するチェーンを繋げます。次に、最終的な結果をgetで取得します。

<?php

namespace App\Http\Controllers;

use Illuminate\Support\Facades\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オブジェクトのインスタンスを結果として含む、Illuminate\Support\Collectionを返します。各カラムの値は、オブジェクトのプロパティとしてアクセスできます。

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

テーブルから１カラム／１レコード取得

データベーステーブルから１レコードのみ取得する必要がある場合は、firstメソッドを使います。このメソッドはStdClassオブジェクトを返します。

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

echo $user->name;

全カラムは必要ない場合、valueメソッドにより一つの値のみ取得できます。このメソッドはカラムの値を直接返します。

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

カラム値をリストで取得

単一カラムの値を値配で取得したい場合はpluckメソッドを使います。以下の例では役割名(title)を配列で取得しています。

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

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

取得配列のキーカラムを指定することもできます。

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

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

結果の分割

数千のデータベースレコードを扱う場合はchunkメソッドの使用を考慮してください。このメソッドは一度に小さな「かたまり(chunk)」で結果を取得し、このチャンクは処理のために「クロージャ」に渡されます。このメソッドは数千のレコードを処理するArtisanコマンドを書くときに便利です。usersレコード全体から一度に１００レコードずつチャンクを処理する例を見てください。

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

クロージャからfalseを返すとチャンクの処理を中断できます。

DB::table('users')->orderBy('id')->chunk(100, function($users) {
    // Process the records...

    return false;
});

集計

またクエリビルダは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');

Selects

Specifying A Select Clause

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

The distinct method allows you to force the query to return distinct results:

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

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

Raw Expressions

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

Joins

Inner Join Clause

The query builder may also be used to write join statements. To perform a basic "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 Clause

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 Clause

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

Advanced Join Clauses

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

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

Unions

The query builder also provides a quick way to "union" two queries together. For example, you may create an initial query and 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();

Tip!! The unionAll method is also available and has the same method signature as union.

Where Clauses

Simple Where Clauses

You may use the where method on a query builder instance to add where clauses to the query. 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. Finally, the third argument is the value to evaluate against the column.

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

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

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

You may also pass an array of conditions to the where function:

$users = DB::table('users')->where([
    ['status', '=', '1'],
    ['subscribed', '<>', '1'],
])->get();

Or Statements

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節

whereBetween

whereBetweenメソッドはカラムの値が２つの値の間である条件を加えます。

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

whereNotBetween

whereNotBetweenメソッドは、カラムの値が２つの値の間ではない条件を加えます。

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

whereDate / whereMonth / whereDay / whereYear

whereDateメソッドはカラム値を日付と比較する時に使用します。

$users = DB::table('users')
                ->whereDate('created_at', '2016-10-10')
                ->get();

whereMonthメソッドはカラム値と、ある年の指定した月とを比較します。

$users = DB::table('users')
                ->whereMonth('created_at', '10')
                ->get();

whereDayメソッドはカラム値と、ある月の指定した日とを比べます。

$users = DB::table('users')
                ->whereDay('created_at', '10')
                ->get();

whereYearメソッドはカラム値と、指定した年とを比べます。

$users = DB::table('users')
                ->whereYear('created_at', '2016')
                ->get();

whereColumn

whereColumnメソッドは２つのカラムが同値である確認をするのに使います。

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

メソッドに比較演算子を追加指定することもできます。

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

whereColumnへ配列により複数の条件を渡すこともできます。各条件はandオペレータでつなげられます。

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

パラメータのグループ分け

時には、"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')

Where Exists節

whereExistsメソッドはWHERE EXISTSの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
)

JSON WHERE節

Laravelはデータベース上のJSONタイプをサポートするカラムに対するクエリに対応しています。現在、MySQL5.7とPostgresに対応しています。JSONカラムをクエリーするには->オペレータを使ってください。

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

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

順序、グループ分け、制限、オフセット

orderBy

orderByメソッドは指定したカラムでクエリ結果をソートします。orderByメソッドの最初の引数はソート対象のカラムで、第２引数はソートの降順(asc)と昇順(desc)をコントロールします。

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

inRandomOrder

inRandomOrderメソッドはクエリ結果をランダムな順番にしたい時に使用します。たとえば、以下のコードはランダムにユーザを一人取得します。

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

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)数のレコードを受け取ったり、結果から指定した件数を飛ばしたりするには、skipとtakeメソッドを使います。

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

別の方法として、limitとoffsetメソッドも使用できます。

$users = DB::table('users')
                ->offset(10)
                ->limit(5)
                ->get();

条件節

ある条件がtrueの場合の時のみ、クエリへ特定の文を適用したい場合があります。例えば特定の入力値がリクエストに含まれている場合に、where文を適用する場合です。whenメソッドで実現できます。

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

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

whenメソッドは、第１引数がtrueの時のみ、指定されたクロージャを実行します。最初の引数がfalseの場合、クロージャを実行しません。

whenメソッドの第3引数に別のクロージャを渡せます。このクロージャは、最初の引数の評価がfalseであると実行されます。この機能をどう使うかを確認するため、クエリのデフォルトソートを設定してみましょう。

$sortBy = null;

$users = DB::table('users')
                ->when($sortBy, function ($query) use ($sortBy) {
                    return $query->orderBy($sortBy);
                }, function ($query) {
                    return $query->orderBy('name');
                })
                ->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]
);

Note: PostgreSQLでinsertGetIdメソッドを使う場合、自動増分カラム名はidである必要があります。他の「シーケンス」からIDを取得したい場合は、insertGetIdメソッドの第２引数にシーケンス名を指定してください。

UPDATE

もちろん、データベースへレコードを挿入するだけでなく、存在しているレコードをupdateメソッドで更新することもできます。updateメソッドはinsertメソッドと同様に、更新対象のカラムのカラム名と値の配列を引数に受け取ります。更新するクエリをwhere節を使って制約することもできます。

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

JSONカラムの更新

JSONカラムを更新する場合、JSONオブジェクトの中の適切なキーへアクセスするために、->記法を使ってください。JSONカラムをサポートしているデータベースでのみ、このオペレータをサポートしています。

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

増減分

クエリビルダは指定したカラムの値を増やしたり、減らしたりするのに便利なメソッドも用意しています。これはシンプルな短縮記法で、update分で書くのに比べるとより記述的であり、簡潔なインターフェイスを提供しています。

両方のメソッドともに、最小１つ、更新したいカラムを引数に取ります。オプションの第２引数はそのカラムの増減値を指定します。

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メソッドで、テーブルからレコードを削除するためにも使用できます。 deleteメソッドを呼び出す前にwhere節を追加し、delete文を制約することもできます。

DB::table('users')->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();