メインコンテンツまでスキップ

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

はじめに​

Laravelのデータベースクエリビルダは、便利で流暢なインターフェースを提供し、データベースクエリの作成と実行を行うことができます。アプリケーション内でほとんどのデータベース操作を実行するために使用でき、すべてのLaravelがサポートするデータベースシステムと完璧に動作します。

LaravelのクエリビルダはPDOパラメータバインディングを使用して、アプリケーションをSQLインジェクション攻撃から保護します。クエリバインディングとして渡される文字列をクリーンアップまたはサニタイズする必要はありません。

警告

PDOは列名のバインディングをサポートしていません。したがって、クエリで参照される列名をユーザー入力で制御させてはいけません。"order by"列を含むクエリによって参照される列名を制御させてはいけません。

データベースクエリの実行​

テーブルからすべての行を取得する​

DB ファサードが提供する table メソッドを使用してクエリを開始できます。table メソッドは、指定されたテーブル用のフルーエントクエリビルダーインスタンスを返し、クエリにさらに制約を追加してから get メソッドを使用してクエリの結果を取得できます：

    <?php

    namespace App\Http\Controllers;

    use Illuminate\Support\Facades\DB;
    use Illuminate\View\View;

    class UserController extends Controller
    {
        /**
         * Show a list of all of the application's users.
         */
        public function index(): View
        {
            $users = DB::table('users')->get();

            return view('user.index', ['users' => $users]);
        }
    }

get メソッドは、クエリの結果を含む Illuminate\Support\Collection インスタンスを返します。各結果は PHP の stdClass オブジェクトのインスタンスです。オブジェクトのプロパティとして列の値にアクセスできます：

    use Illuminate\Support\Facades\DB;

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

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

注記

Laravel コレクションには、データのマッピングや縮小に非常に強力なメソッドが多数用意されています。Laravel コレクションについての詳細は、コレクションのドキュメントを参照してください。

テーブルから単一の行/列を取得する​

データベーステーブルから単一の行を取得するだけの場合は、DB ファサードの first メソッドを使用できます。このメソッドは単一の stdClass オブジェクトを返します：

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

    return $user->email;

行全体が不要な場合は、value メソッドを使用してレコードから単一の値を抽出できます。このメソッドは列の値を直接返します：

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

id 列の値によって単一の行を取得するには、find メソッドを使用します：

    $user = DB::table('users')->find(3);

列の値のリストを取得する​

単一の列の値を含む Illuminate\Support\Collection インスタンスを取得したい場合は、pluck メソッドを使用できます。この例では、ユーザーのタイトルのコレクションを取得します：

    use Illuminate\Support\Facades\DB;

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

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

 You may specify the column that the resulting collection should use as its keys by providing a second argument to the `pluck` method:

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

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

結果のチャンキング​

数千のデータベースレコードを処理する必要がある場合は、DB ファサードが提供する chunk メソッドを使用することを検討してください。このメソッドは、一度に小さなチャンクの結果を取得し、各チャンクを処理用のクロージャにフィードします。例えば、100レコードずつのチャンクで users テーブル全体を取得する場合は次のようにします：

    use Illuminate\Support\Collection;
    use Illuminate\Support\Facades\DB;

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

追加のチャンクが処理されないようにするには、クロージャから false を返すことができます：

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

        return false;
    });

結果をチャンク分割しながらデータベースレコードを更新する場合、チャンク結果が予期しない方法で変更される可能性があります。チャンク分割中に取得したレコードを更新する予定がある場合は、常に chunkById メソッドを使用することが最善です。このメソッドは、レコードの主キーに基づいて結果を自動的にページ分割します：

    DB::table('users')->where('active', false)
        ->chunkById(100, function (Collection $users) {
            foreach ($users as $user) {
                DB::table('users')
                    ->where('id', $user->id)
                    ->update(['active' => true]);
            }
        });

警告

チャンクコールバック内でレコードを更新または削除する場合、主キーまたは外部キーへの変更がチャンククエリに影響を与える可能性があります。これにより、チャンク分割された結果に含まれないレコードが発生する可能性があります。

ストリーミング結果の遅延読み込み​

lazy メソッドは、chunk メソッドと同様に、クエリをチャンク単位で実行します。ただし、各チャンクをコールバックに渡す代わりに、lazy() メソッドは LazyCollection を返し、結果を単一のストリームとして操作できます：

use Illuminate\Support\Facades\DB;

DB::table('users')->orderBy('id')->lazy()->each(function (object $user) {
    // ...
});

再度、取得したレコードを反復処理しながら更新する予定がある場合は、lazyById または lazyByIdDesc メソッドを使用することが最善です。これらのメソッドは、レコードの主キーに基づいて結果を自動的にページ分割します：

DB::table('users')->where('active', false)
    ->lazyById()->each(function (object $user) {
        DB::table('users')
            ->where('id', $user->id)
            ->update(['active' => true]);
    });
警告

レコードを反復処理しながら更新または削除する場合、主キーまたは外部キーへの変更がクエリに影響を与える可能性があります。これにより、結果に含まれないレコードが発生する可能性があります。

集計​

クエリビルダーは、count、max、min、avg、sum などの集計値を取得するためのさまざまなメソッドも提供しています。クエリを構築した後にこれらのメソッドのいずれかを呼び出すことができます：

    use Illuminate\Support\Facades\DB;

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

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

もちろん、これらのメソッドを他の句と組み合わせて、集計値の計算方法を微調整することもできます：

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

レコードの存在を判定する​

クエリの制約に一致するレコードが存在するかどうかを判定するために count メソッドを使用する代わりに、exists メソッドと doesntExist メソッドを使用することができます：

    if (DB::table('orders')->where('finalized', 1)->exists()) {
        // ...
    }

    if (DB::table('orders')->where('finalized', 1)->doesntExist()) {
        // ...
    }

選択文​

選択句の指定​

常にデータベーステーブルからすべての列を選択したいわけではありません。select メソッドを使用すると、クエリにカスタムの "select" 句を指定することができます：

    use Illuminate\Support\Facades\DB;

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

distinct メソッドを使用すると、クエリが重複のない結果を返すように強制することができます：

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

すでにクエリビルダのインスタンスを持っており、その既存の選択句に列を追加したい場合は、addSelect メソッドを使用することができます：

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

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

生の式​

時々、クエリに任意の文字列を挿入する必要があるかもしれません。生の文字列式を作成するには、DB ファサードが提供する raw メソッドを使用することができます：

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

警告

生のステートメントは文字列としてクエリに挿入されるため、SQLインジェクションの脆弱性を作成しないように非常に注意する必要があります。

生のメソッド​

DB::raw メソッドを使用する代わりに、クエリのさまざまな部分に生の式を挿入するために次のメソッドを使用することもできます。Laravel は生の式を使用するクエリがSQLインジェクションの脆弱性に対して保護されていることを保証できません。

selectRaw​

selectRaw メソッドは addSelect(DB::raw(/* ... */)) の代わりに使用することができます。このメソッドは第二引数としてオプションのバインディング配列を受け入れます：

    $orders = DB::table('orders')
                    ->selectRaw('price * ? as price_with_tax', [1.0825])
                    ->get();

whereRaw / orWhereRaw​

whereRaw メソッドと orWhereRaw メソッドは、クエリに生の "where" 句を挿入するために使用することができます。これらのメソッドは第二引数としてオプションのバインディング配列を受け入れます：

    $orders = DB::table('orders')
                    ->whereRaw('price > IF(state = "TX", ?, 100)', [200])
                    ->get();

havingRaw / orHavingRaw​

havingRawおよびorHavingRawメソッドは、"having"句の値として生の文字列を提供するために使用できます。これらのメソッドは、第2引数としてオプションのバインディング配列を受け入れます。

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

orderByRaw​

orderByRawメソッドは、"order by"句の値として生の文字列を提供するために使用できます。

    $orders = DB::table('orders')
                    ->orderByRaw('updated_at - created_at DESC')
                    ->get();

groupByRaw​

groupByRawメソッドは、group by句の値として生の文字列を提供するために使用できます。

    $orders = DB::table('orders')
                    ->select('city', 'state')
                    ->groupByRaw('city, state')
                    ->get();

Joins​

Inner Join Clause​

クエリビルダは、クエリに結合句を追加するためにも使用できます。基本的な"inner join"を実行するには、クエリビルダインスタンスでjoinメソッドを使用します。joinメソッドに渡される最初の引数は、結合する必要があるテーブルの名前であり、残りの引数は結合のための列の制約を指定します。1つのクエリで複数のテーブルを結合することもできます。

    use Illuminate\Support\Facades\DB;

    $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 / Right Join Clause​

"inner join"の代わりに"left join"または"right join"を実行したい場合は、leftJoinメソッドまたはrightJoinメソッドを使用します。これらのメソッドはjoinメソッドと同じシグネチャを持ちます。

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

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

Cross Join Clause​

"cross join"を実行するには、crossJoinメソッドを使用できます。Cross joinは最初のテーブルと結合されるテーブルのデカルト積を生成します。

    $sizes = DB::table('sizes')
                ->crossJoin('colors')
                ->get();

Advanced Join Clauses​

より高度な結合句も指定できます。始めるには、joinメソッドの第2引数としてクロージャを渡します。クロージャはIlluminate\Database\Query\JoinClauseインスタンスを受け取り、"join"句に制約を指定できます。

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

"where"句を結合に使用する場合は、JoinClauseインスタンスが提供するwhereメソッドとorWhereメソッドを使用できます。これらのメソッドは、2つの列を比較するのではなく、列を値と比較します。

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

サブクエリの結合​

joinSub、leftJoinSub、rightJoinSubメソッドを使用して、クエリをサブクエリに結合できます。これらのメソッドは、サブクエリ、そのテーブルエイリアス、関連する列を定義するクロージャの3つの引数を受け取ります。この例では、ユーザーのコレクションを取得し、各ユーザーレコードには、ユーザーの最新のブログ投稿のcreated_atタイムスタンプも含まれます。

    $latestPosts = DB::table('posts')
                       ->select('user_id', DB::raw('MAX(created_at) as last_post_created_at'))
                       ->where('is_published', true)
                       ->groupBy('user_id');

    $users = DB::table('users')
            ->joinSub($latestPosts, 'latest_posts', function (JoinClause $join) {
                $join->on('users.id', '=', 'latest_posts.user_id');
            })->get();

ラテラル結合​

警告

ラテラル結合は現在、PostgreSQL、MySQL >= 8.0.14、およびSQL Serverでサポートされています。

"ラテラル結合"を実行するには、joinLateralおよびleftJoinLateralメソッドを使用できます。これらのメソッドは、サブクエリとそのテーブルエイリアスの2つの引数を受け取ります。結合条件は、指定されたサブクエリのwhere句内に指定する必要があります。ラテラル結合は各行ごとに評価され、サブクエリの外部の列を参照できます。

この例では、ユーザーのコレクションと、ユーザーの最新の3つのブログ投稿を取得します。各ユーザーは、最新のブログ投稿ごとに最大3行の結果セットを生成できます。結合条件は、サブクエリ内のwhereColumn句で指定され、現在のユーザー行を参照します。

    $latestPosts = DB::table('posts')
                       ->select('id as post_id', 'title as post_title', 'created_at as post_created_at')
                       ->whereColumn('user_id', 'users.id')
                       ->orderBy('created_at', 'desc')
                       ->limit(3);

    $users = DB::table('users')
                ->joinLateral($latestPosts, 'latest_posts')
                ->get();

結合​

クエリビルダーには、2つ以上のクエリを便利に「結合」するメソッドも提供されています。たとえば、初期クエリを作成し、unionメソッドを使用して他のクエリと結合できます。

    use Illuminate\Support\Facades\DB;

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

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

unionメソッドに加えて、クエリビルダーにはunionAllメソッドも提供されています。unionAllメソッドを使用して結合されたクエリは、重複する結果が削除されません。unionAllメソッドは、unionメソッドと同じメソッドシグネチャを持っています。

基本的なWhere句​

Where句​

クエリビルダの where メソッドを使用して、クエリに "where" 句を追加することができます。where メソッドへの最も基本的な呼び出しには3つの引数が必要です。最初の引数は列の名前です。2番目の引数はオペレータであり、データベースでサポートされているオペレータのいずれかです。3番目の引数は列の値と比較する値です。

例えば、次のクエリは、votes 列の値が 100 であり、age 列の値が 35 よりも大きいユーザーを取得します:

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

便宜上、特定の値に等しい列を検証したい場合は、値を where メソッドの2番目の引数として渡すことができます。Laravel は = オペレータを使用したいと仮定します:

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

前述のように、データベースシステムでサポートされている任意のオペレータを使用できます:

    $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 関数に条件の配列を渡すこともできます。配列の各要素は通常 where メソッドに渡される3つの引数を含む配列である必要があります:

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

警告

PDO は列名のバインディングをサポートしていません。そのため、クエリで参照される列名をユーザー入力で指定してはいけません。"order by" 列も含めて。

Or Where句​

クエリビルダの where メソッドに連続して呼び出しを連結する場合、"where" 句は and オペレータを使用して結合されます。ただし、orWhere メソッドを使用して、or オペレータを使用してクエリに句を結合することができます。orWhere メソッドは where メソッドと同じ引数を受け入れます:

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

"or" 条件を括弧でグループ化する必要がある場合は、orWhere メソッドの最初の引数としてクロージャを渡すことができます:

    $users = DB::table('users')
                ->where('votes', '>', 100)
                ->orWhere(function (Builder $query) {
                    $query->where('name', 'Abigail')
                          ->where('votes', '>', 50);
                })
                ->get();

上記の例は、次のSQLを生成します。

select * from users where votes > 100 or (name = 'Abigail' and votes > 50)
警告

orWhere呼び出しを常にグループ化してください。グローバルスコープが適用されると予期しない動作を回避するためです。

Where Not Clauses​

whereNotおよびorWhereNotメソッドは、指定されたクエリ制約を否定するために使用できます。たとえば、次のクエリは、クリアランス対象外の製品または価格が10未満の製品を除外します：

    $products = DB::table('products')
                    ->whereNot(function (Builder $query) {
                        $query->where('clearance', true)
                              ->orWhere('price', '<', 10);
                    })
                    ->get();

Where Any / All Clauses​

時には、複数の列に同じクエリ制約を適用する必要がある場合があります。たとえば、指定されたリスト内の任意の列が指定された値にLIKEされているレコードを取得したい場合があります。これは、whereAnyメソッドを使用して行うことができます：

    $users = DB::table('users')
                ->where('active', true)
                ->whereAny([
                    'name',
                    'email',
                    'phone',
                ], 'LIKE', 'Example%')
                ->get();

上記のクエリは、次のSQLを生成します：

SELECT *
FROM users
WHERE active = true AND (
    name LIKE 'Example%' OR
    email LIKE 'Example%' OR
    phone LIKE 'Example%'
)

同様に、whereAllメソッドを使用して、指定されたすべての列が指定された制約に一致するレコードを取得できます：

    $posts = DB::table('posts')
                ->where('published', true)
                ->whereAll([
                    'title',
                    'content',
                ], 'LIKE', '%Laravel%')
                ->get();

上記のクエリは、次のSQLを生成します：

SELECT *
FROM posts
WHERE published = true AND (
    title LIKE '%Laravel%' AND
    content LIKE '%Laravel%'
)

JSON Where Clauses​

Laravelは、JSON列タイプをサポートするデータベースでのJSON列のクエリもサポートしています。現在、これにはMySQL 8.0+、PostgreSQL 12.0+、SQL Server 2017+、およびSQLite 3.39.0+（JSON1拡張機能を使用）が含まれます。JSON列をクエリするには、->演算子を使用します：

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

JSON配列をクエリするには、whereJsonContainsを使用できます：

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

アプリケーションがMySQLまたはPostgreSQLデータベースを使用している場合、whereJsonContainsメソッドに値の配列を渡すことができます：

    $users = DB::table('users')
                    ->whereJsonContains('options->languages', ['en', 'de'])
                    ->get();

JSON配列の長さでクエリするには、whereJsonLengthメソッドを使用できます：

    $users = DB::table('users')
                    ->whereJsonLength('options->languages', 0)
                    ->get();

    $users = DB::table('users')
                    ->whereJsonLength('options->languages', '>', 1)
                    ->get();

追加のWhere句​

whereBetween / orWhereBetween

whereBetweenメソッドは、列の値が2つの値の間にあることを検証します：

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

whereNotBetween / orWhereNotBetween

whereNotBetweenメソッドは、列の値が2つの値の外側にあることを検証します：

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

whereBetweenColumns / whereNotBetweenColumns / orWhereBetweenColumns / orWhereNotBetweenColumns

whereBetweenColumnsメソッドは、同じテーブル行の2つの列の値の間にあることを検証します：

    $patients = DB::table('patients')
                           ->whereBetweenColumns('weight', ['minimum_allowed_weight', 'maximum_allowed_weight'])
                           ->get();

whereNotBetweenColumnsメソッドは、同じテーブル行の2つの列の値の外側にあることを検証します：

    $patients = DB::table('patients')
                           ->whereNotBetweenColumns('weight', ['minimum_allowed_weight', 'maximum_allowed_weight'])
                           ->get();

whereIn / whereNotIn / orWhereIn / orWhereNotIn

whereInメソッドは、指定された列の値が指定された配列内に含まれていることを検証します：

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

whereNotInメソッドは、指定された列の値が指定された配列に含まれていないことを検証します：

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

whereInメソッドの第2引数にクエリオブジェクトを指定することもできます：

    $activeUsers = DB::table('users')->select('id')->where('is_active', 1);

    $users = DB::table('comments')
                        ->whereIn('user_id', $activeUsers)
                        ->get();

上記の例は、次のSQLを生成します：

select * from comments where user_id in (
    select id
    from users
    where is_active = 1
)
警告

クエリに大量の整数バインディングを追加する場合は、whereIntegerInRawまたはwhereIntegerNotInRawメソッドを使用してメモリ使用量を大幅に削減できます。

whereNull / whereNotNull / orWhereNull / orWhereNotNull

whereNullメソッドは、指定された列の値がNULLであることを検証します：

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

whereNotNullメソッドは、列の値がNULLでないことを検証します：

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

whereDate / whereMonth / whereDay / whereYear / whereTime

whereDateメソッドは、列の値を日付と比較するために使用できます：

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

whereMonthメソッドは、列の値を特定の月と比較するために使用できます：

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

whereDayメソッドは、列の値を月の特定の日と比較するために使用できます：

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

whereYearメソッドは、特定の年と比較して列の値を比較するために使用できます：

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

whereTimeメソッドは、列の値を特定の時間と比較するために使用できます：

    $users = DB::table('users')
                    ->whereTime('created_at', '=', '11:20:45')
                    ->get();

whereColumn / orWhereColumn

whereColumnメソッドは、2つの列が等しいことを検証するために使用できます：

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

whereColumnメソッドに比較演算子を渡すこともできます：

    $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" 条件を括弧でグループ化する必要があります。実際、orWhereメソッドへの呼び出しは常に括弧でグループ化することが推奨されます。これにより、予期しないクエリの動作を回避できます。これを実現するために、whereメソッドにクロージャを渡すことができます：

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

whereメソッドにクロージャを渡すと、クエリビルダーが制約グループを開始するように指示されます。クロージャはクエリビルダーインスタンスを受け取り、括弧グループ内に含まれるべき制約を設定するために使用できます。上記の例は、次のSQLを生成します：

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

グローバルスコープが適用されている場合、予期しない動作を避けるために、常にorWhere呼び出しをグループ化する必要があります。

高度なWhere句​

Where Exists句​

whereExistsメソッドを使用すると、"where exists" SQL句を記述できます。whereExistsメソッドは、"exists"句内に配置するクエリを定義するために、クエリビルダーインスタンスを受け取るクロージャを受け入れます：

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

また、クロージャの代わりにwhereExistsメソッドにクエリオブジェクトを提供することもできます：

    $orders = DB::table('orders')
                    ->select(DB::raw(1))
                    ->whereColumn('orders.user_id', 'users.id');

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

上記の例は、以下のSQLを生成します：

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

サブクエリのWhere句​

時々、サブクエリの結果を特定の値と比較する "where" 句を構築する必要があります。これは、where メソッドにクロージャと値を渡すことで実現できます。たとえば、次のクエリは、指定されたタイプの最近の "membership" を持つすべてのユーザーを取得します：

    use App\Models\User;
    use Illuminate\Database\Query\Builder;

    $users = User::where(function (Builder $query) {
        $query->select('type')
            ->from('membership')
            ->whereColumn('membership.user_id', 'users.id')
            ->orderByDesc('membership.start_date')
            ->limit(1);
    }, 'Pro')->get();

または、列をサブクエリの結果と比較する "where" 句を構築する必要があるかもしれません。これは、where メソッドに列、演算子、およびクロージャを渡すことで実現できます。たとえば、次のクエリは、金額が平均未満のすべての収入レコードを取得します：

    use App\Models\Income;
    use Illuminate\Database\Query\Builder;

    $incomes = Income::where('amount', '<', function (Builder $query) {
        $query->selectRaw('avg(i.amount)')->from('incomes as i');
    })->get();

フルテキストのWhere句​

警告

フルテキストのWhere句は現在、MySQLとPostgreSQLでサポートされています。

whereFullText メソッドと orWhereFullText メソッドを使用して、フルテキストインデックスを持つ列に対してクエリにフルテキスト "where" 句を追加できます。これらのメソッドは、Laravelによって基礎となるデータベースシステムのための適切なSQLに変換されます。たとえば、MySQLを利用するアプリケーションでは、MATCH AGAINST 句が生成されます：

    $users = DB::table('users')
               ->whereFullText('bio', 'web developer')
               ->get();

ソート、グループ化、制限、オフセット​

ソート​

orderBy メソッド​

orderBy メソッドを使用すると、クエリの結果を指定された列でソートできます。orderBy メソッドが受け入れる最初の引数は、ソートしたい列であり、2番目の引数はソートの方向を決定し、asc または desc のいずれかである必要があります：

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

複数の列でソートするには、必要に応じて単純に orderBy を複数回呼び出すことができます：

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

latest メソッドと oldest メソッド​

latest メソッドと oldest メソッドを使用すると、日付で結果を簡単に並べ替えることができます。デフォルトでは、結果はテーブルの created_at カラムで並べ替えられます。または、並べ替えたいカラム名を渡すこともできます：

    $user = DB::table('users')
                    ->latest()
                    ->first();

ランダムな順序​

inRandomOrder メソッドを使用して、クエリ結果をランダムに並べ替えることができます。たとえば、このメソッドを使用してランダムなユーザーを取得できます：

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

既存の並べ替えの削除​

reorder メソッドは、以前にクエリに適用されたすべての "order by" 句を削除します：

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

    $unorderedUsers = $query->reorder()->get();

reorder メソッドを呼び出す際に、クエリに完全に新しい順序を適用するために、カラムと方向を渡すことができます：

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

    $usersOrderedByEmail = $query->reorder('email', 'desc')->get();

グループ化​

groupBy および having メソッド​

groupBy メソッドと having メソッドを使用して、クエリ結果をグループ化できます。having メソッドのシグネチャは where メソッドと似ています：

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

havingBetween メソッドを使用して、指定された範囲内の結果をフィルタリングできます：

    $report = DB::table('orders')
                    ->selectRaw('count(id) as number_of_orders, customer_id')
                    ->groupBy('customer_id')
                    ->havingBetween('number_of_orders', [5, 15])
                    ->get();

複数の引数を groupBy メソッドに渡すことで、複数のカラムでグループ化できます：

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

より高度な having ステートメントを構築するには、havingRaw メソッドを参照してください。

制限とオフセット​

skip および take メソッド​

skip メソッドと take メソッドを使用して、クエリから返される結果の数を制限したり、クエリで指定された結果の数をスキップしたりできます：

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

代わりに、limit メソッドと offset メソッドを使用することもできます。これらのメソッドは、それぞれ take メソッドと skip メソッドと機能的に同等です：

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

条件付き句​

時には、特定のクエリ句を別の条件に基づいてクエリに適用したいことがあります。たとえば、着信 HTTP リクエストに指定された入力値が存在する場合にのみ where ステートメントを適用したい場合があります。これは、when メソッドを使用して実現できます：

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

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

when メソッドは、最初の引数が true の場合にのみ指定されたクロージャを実行します。最初の引数が false の場合、クロージャは実行されません。したがって、上記の例では、when メソッドに与えられたクロージャは、着信リクエストに role フィールドが存在し、かつ true に評価された場合にのみ呼び出されます。

when メソッドには、3番目の引数として別のクロージャを渡すこともできます。このクロージャは、最初の引数が false と評価された場合にのみ実行されます。この機能の使用方法を示すために、クエリのデフォルトの並べ替えを設定するために使用します：

    $sortByVotes = $request->boolean('sort_by_votes');

    $users = DB::table('users')
                    ->when($sortByVotes, function (Builder $query, bool $sortByVotes) {
                        $query->orderBy('votes');
                    }, function (Builder $query) {
                        $query->orderBy('name');
                    })
                    ->get();

挿入ステートメント​

クエリビルダーは、データベーステーブルにレコードを挿入するために使用できる insert メソッドも提供しています。insert メソッドは、カラム名と値の配列を受け入れます：

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

配列の配列を渡すことで一度に複数のレコードを挿入することができます。各配列は、テーブルに挿入するべきレコードを表します：

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

insertOrIgnore メソッドは、データベースにレコードを挿入する際にエラーを無視します。このメソッドを使用する際には、重複レコードエラーが無視され、データベースエンジンによっては他の種類のエラーも無視される可能性があることに注意する必要があります。たとえば、insertOrIgnore は MySQL の厳密モードをバイパス します：

    DB::table('users')->insertOrIgnore([
        ['id' => 1, 'email' => 'sisko@example.com'],
        ['id' => 2, 'email' => 'archer@example.com'],
    ]);

insertUsing メソッドは、サブクエリを使用して挿入すべきデータを決定しながら、新しいレコードをテーブルに挿入します：

    DB::table('pruned_users')->insertUsing([
        'id', 'name', 'email', 'email_verified_at'
    ], DB::table('users')->select(
        'id', 'name', 'email', 'email_verified_at'
    )->where('updated_at', '<=', now()->subMonth()));

自動増分 ID​

テーブルに自動増分 ID がある場合は、insertGetId メソッドを使用してレコードを挿入し、その後 ID を取得します：

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

警告

PostgreSQLを使用する場合、insertGetIdメソッドは、自動増分列がidという名前であることを期待します。異なる"シーケンス"からIDを取得したい場合は、insertGetIdメソッドの第2引数として列名を渡すことができます。

Upserts​

upsertメソッドは、存在しないレコードを挿入し、すでに存在するレコードを新しい値で更新します。メソッドの第1引数は挿入または更新する値であり、第2引数は関連するテーブル内のレコードを一意に識別する列をリストアップします。メソッドの第3引数は、データベース内で既存のレコードが一致する場合に更新する列の配列です：

    DB::table('flights')->upsert(
        [
            ['departure' => 'Oakland', 'destination' => 'San Diego', 'price' => 99],
            ['departure' => 'Chicago', 'destination' => 'New York', 'price' => 150]
        ],
        ['departure', 'destination'],
        ['price']
    );

上記の例では、Laravelは2つのレコードを挿入しようとします。departureとdestinationの列値が同じ既存のレコードが存在する場合、Laravelはそのレコードのprice列を更新します。

警告

SQL Server以外のすべてのデータベースは、upsertメソッドの第2引数の列に"primary"または"unique"インデックスが必要です。さらに、MySQLデータベースドライバはupsertメソッドの第2引数を無視し、常にテーブルの"primary"および"unique"インデックスを使用して既存のレコードを検出します。

Update Statements​

データベースにレコードを挿入するだけでなく、クエリビルダはupdateメソッドを使用して既存のレコードを更新することもできます。updateメソッドは、insertメソッドと同様に、更新する列と値のペアの配列を受け入れます。updateメソッドは、影響を受けた行の数を返します。updateクエリをwhere句で制約することができます：

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

Update or Insert​

場合によっては、データベース内の既存のレコードを更新したいか、一致するレコードが存在しない場合は作成したい場合があります。このシナリオでは、updateOrInsertメソッドを使用できます。updateOrInsertメソッドは、レコードを見つけるための条件の配列と、更新する列と値のペアの配列を受け入れます。

updateOrInsertメソッドは、最初の引数の列と値のペアを使用して一致するデータベースレコードを検索しようとします。レコードが存在する場合、2番目の引数の値で更新されます。レコードが見つからない場合、両引数のマージされた属性で新しいレコードが挿入されます：

    DB::table('users')
        ->updateOrInsert(
            ['email' => 'john@example.com', 'name' => 'John'],
            ['votes' => '2']
        );

updateOrInsertメソッドにクロージャを提供して、一致するレコードの存在に基づいてデータベースに挿入または更新される属性をカスタマイズすることができます：

DB::table('users')->updateOrInsert(
    ['user_id' => $user_id],
    fn ($exists) => $exists ? [
        'name' => $data['name'],
        'email' => $data['email'],
    ] : [
        'name' => $data['name'],
        'email' => $data['email'],
        'marketable' => true,
    ],
);

JSON列の更新​

JSON列を更新する場合、JSONオブジェクト内の適切なキーを更新するために->構文を使用する必要があります。この操作はMySQL 5.7+およびPostgreSQL 9.5+でサポートされています：

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

インクリメントおよびデクリメント​

クエリビルダーは、指定された列の値をインクリメントまたはデクリメントする便利なメソッドも提供しています。これらのメソッドは少なくとも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']);

さらに、incrementEachおよびdecrementEachメソッドを使用して複数の列を一度にインクリメントまたはデクリメントすることもできます：

    DB::table('users')->incrementEach([
        'votes' => 5,
        'balance' => 100,
    ]);

削除ステートメント​

クエリビルダーのdeleteメソッドを使用して、テーブルからレコードを削除することができます。deleteメソッドは影響を受けた行の数を返します。deleteメソッドを呼び出す前に「where」句を追加してdeleteステートメントを制約することができます：

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

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

テーブル全体を切り捨てて、テーブルからすべてのレコードを削除し、自動インクリメントIDをゼロにリセットする場合は、truncateメソッドを使用できます：

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

テーブルの切り捨てとPostgreSQL​

PostgreSQLデータベースを切り捨てると、CASCADE動作が適用されます。これは、他のテーブルの外部キー関連レコードも削除されることを意味します。

悲観的ロック​

クエリビルダには、selectステートメントを実行する際に「悲観的ロック」を実現するためのいくつかの関数も含まれています。共有ロックでステートメントを実行するには、sharedLockメソッドを呼び出すことができます。共有ロックは、トランザクションがコミットされるまで選択された行の変更を防ぎます：

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

または、lockForUpdateメソッドを使用することもできます。"for update"ロックは、選択されたレコードが変更されるのを防ぎ、または他の共有ロックで選択されるのを防ぎます：

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

デバッグ​

クエリをビルドする際にddメソッドとdumpメソッドを使用して、現在のクエリのバインディングとSQLをダンプすることができます。ddメソッドはデバッグ情報を表示してからリクエストの実行を停止します。dumpメソッドはデバッグ情報を表示しますが、リクエストの実行を続行します：

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

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

dumpRawSqlメソッドとddRawSqlメソッドは、クエリに対して呼び出すことで、すべてのパラメータバインディングが適切に置換されたクエリのSQLをダンプすることができます：

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

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