Laravel 9.x データベース：クエリビルダ

イントロダクション

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

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

Warning!! PDOはカラム名のバインドをサポートしていません。したがって、"order by"カラムを含む、クエリが参照するカラム名をユーザー入力で指定できないようにする必要があります。

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

テーブルからの全行の取得

DBファサードが提供するtableメソッドを使用してクエリの最初に使用します。tableメソッドは、指定するテーブルの流暢(fluent)なクエリビルダインスタンスを返します。これによりクエリにさらに制約をチェーンし、最後にgetメソッドを使用してクエリの結果を取得できます。

<?php

namespace App\Http\Controllers;

use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\DB;

class UserController extends Controller
{
    /**
     * アプリケーションの全ユーザーをリスト表示
     *
     * @return \Illuminate\Http\Response
     */
    public function index()
    {
        $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;
}

Note: 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;
}

pluckメソッドに２番目の引数を指定し、結果のコレクションがキーとして使用する列を指定できます。

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

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

結果の分割

何千ものデータベースレコードを処理する必要がある場合は、DBファサードが提供するchunkメソッドの使用を検討してください。このメソッドは、一回に小さな結果のチャンク（小間切れ）を取得し、各チャンクをクロージャで処理するために送ります。たとえば、usersテーブル全体を一回で100レコードのチャンクで取得してみましょう。

use Illuminate\Support\Facades\DB;

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

クロージャから falseを返えせば、それ以上のチャンクの処理を停止できます。

DB::table('users')->orderBy('id')->chunk(100, function ($users) {
    // レコードの処理…

    return false;
});

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

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

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

ルーズなストリーミング結果

lazyメソッドは、チャンク単位でクエリを実行するという意味で、chunkメソッドと似たような動作をします。しかし、各チャンクをコールバックに渡すのではなく、lazy()メソッドは LazyCollectionを返し、結果をひとつのストリームとして扱えます。

use Illuminate\Support\Facades\DB;

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

繰り返しになりますが、検索したレコードを反復しながら更新する予定がある場合は、代わりにlazyByIdかlazyByIdDescメソッドの使用を推奨します。これらのメソッドは、レコードの主キーに基づいて結果を自動的にページ分割します。

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

Warning!! レコードの反復処理中にレコードの更新や削除を行うと、主キーや外部キーの変更がチャンククエリへ影響を与える可能性があります。これにより、レコードが結果に含まれない可能性があります。

集計

クエリビルダは、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句の指定

データベーステーブルからすべての列を選択する必要があるとは限りません。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();

クエリビルダインスタンスがすでにあり、既存のSELECT句にカラムを追加する場合は、addSelectメソッドを使用できます。

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

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

素のSQL

クエリへ任意の文字列を挿入する必要のある場合があります。素の文字列式を作成するには、DBファサードが提供するrawメソッドを使用します。

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

Warning!! 素のSQL文はそのまま文字列としてクエリへ挿入されるため、SQLインジェクションの脆弱性を含めぬように細心の注意を払う必要があります。

rawメソッド

DB::rawメソッドを使用する代わりに以降のメソッドを使用して、クエリのさまざまな部分に素のSQL式を挿入することもできます。素の式を使用するクエリでは、SQLインジェクションの脆弱性からの保護をLaravelは保証しないことに注意してください。

selectRaw

addSelect(DB::raw(/*...*/))の代わりにselectRawメソッドを使用できます。このメソッドは、２番目の引数にバインディングのオプションの配列を取ります。

$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"句の値として素の文字列を指定できます。これらのメソッドは、２番目の引数にバインディングのオプションの配列を取ります。

$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メソッドを使用して、groupby句の値として素の文字列を指定できます。

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

JOIN

INNER JOIN句

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

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句

"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句

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

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

上級JOIN句

より高度なJOIN句を指定することもできます。そのためには、joinメソッドの２番目の引数にクロージャを渡します。クロージャはIlluminate\Database\Query\JoinClauseインスタンスを受け取ります。これにより、"join"句に制約を指定できます。

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

テーブル結合で"where"句を使用する場合は、JoinClauseインスタンスが提供するwhereとorWhereメソッドを使用します。２つのカラムを比較する代わりに、これらのメソッドはカラムを値と比較します。

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

サブクエリのJOIN

joinSub、leftJoinSub、rightJoinSubメソッドを使用して、クエリをサブクエリに結合できます。各メソッドは、サブクエリ、そのテーブルエイリアス、および関連するカラムを定義するクロージャの３引数を取ります。この例では、各ユーザーレコードにユーザーの最後に公開されたブログ投稿の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 ($join) {
            $join->on('users.id', '=', 'latest_posts.user_id');
        })->get();

UNION

クエリビルダは、２つ以上のクエリを「結合(union)」するために便利な方法も提供します。たとえば、最初のクエリを作成したあとで、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メソッドのもっとも基本的な呼び出しには、３つの引数が必要です。最初の引数はカラムの名前です。２番目の引数は演算子であり、データベースがサポートしている任意の演算子が指定できます。３番目の引数はカラムの値と比較する値です。

たとえば、以下のクエリはvotes列の値が100に等しく、age列の値が35より大きいユーザーを取得します。

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

使いやすいように、カラムが特定の値に対して=であることを確認する場合は、その値を２番目の引数としてwhereメソッドに渡すことができます。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メソッドに渡す３つの引数を含む配列である必要があります。

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

Warning!! 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($query) {
                $query->where('name', 'Abigail')
                      ->where('votes', '>', 50);
            })
            ->get();

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

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

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

WHERE NOT句

whereNotとorWhereNotメソッドを使用すると、指定したクエリ制約のグループを否定できます。例えば、以下のクエリは、クリアランス品や、価格が１０以下の商品を除外します。

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

JSON WHERE句

Laravelは、JSONカラム型のサポートを提供するデータベースで、JSONカラム型のクエリもサポートしています。現在、MySQL5.7以上、PostgreSQL、SQL Server 2016、SQLite3.39.0（JSON1拡張を使用）がこれに該当します。JSONカラムをクエリするには、->演算子を使用します。

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

whereJsonContainsを使用してJSON配列をクエリできます。この機能は、バージョン3.38.0以下のSQLiteデータベースではサポートしていません。

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

アプリケーションがMySQLまたはPostgreSQLデータベースを使用している場合は、値の配列をwhereJsonContainsメソッドで渡してください。

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

whereJsonLengthメソッドを使用して、JSON配列をその長さでクエリできます。

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

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

その他のWHERE句

whereBetween／orWhereBetween

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

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

whereNotBetween／orWhereNotBetween

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

$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メソッドの第２引数へ、クエリオブジェクトを指定することもできます。

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

Warning!! クエリに整数バインディングの大きな配列を追加する場合は、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メソッドは、２つのカラムが等しい条件を加えます。

$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 ($query) {
               $query->where('votes', '>', 100)
                     ->orWhere('title', '=', 'Admin');
           })
           ->get();

ご覧のとおり、クロージャをwhereメソッドへ渡すことで、クエリビルダへ制約のグループ化を開始するように指示しています。クロージャは、括弧グループ内に含める必要のある制約を構築するために使用するクエリビルダインスタンスを受け取ります。上記の例では、次のSQLが生成されます。

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

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

上級WHERE節

WHERE EXISTS句

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

$users = DB::table('users')
           ->whereExists(function ($query) {
               $query->select(DB::raw(1))
                     ->from('orders')
                     ->whereColumn('orders.user_id', 'users.id');
           })
           ->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;

$users = User::where(function ($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;

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

フルテキストのWHERE句

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

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

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

順序、グループ化、件数制限、オフセット

順序

orderByメソッド

orderByメソッドを使用すると、クエリの結果を特定のカラムで並べ替えできます。orderByメソッドの最初の引数は、並べ替えるカラムです。２番目の引数は、並べ替えの方向を決定し、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メソッドを呼び出すときにカラムと方向を渡して、既存の"order by"句をすべて削除しから、クエリにまったく新しい順序を適用することもできます。

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

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

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

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

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

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

INSERT文

クエリビルダは、データベーステーブルにレコードを挿入するために使用できる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のstrictモードをバイパスします。

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

Warning!! PostgreSQLを使用する場合、insertGetIdメソッドは自動増分カラムがidという名前であると想定します。別の「シーケンス」からIDを取得する場合は、insertGetIdメソッドの第２パラメータとしてカラム名を渡してください。

UPSERTS

upsert (update+insert)メソッドは、存在しない場合はレコードを挿入し、指定した新しい値でレコードを更新します。メソッドの最初の引数は、挿入または更新する値で構成され、２番目の引数は、関連付けたテーブル内のレコードを一意に識別するカラムをリストします。第３引数は、一致するレコードがデータベースですでに存在する場合に、更新する必要があるカラムの配列です。

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

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

Warning!! SQL Serverを除くすべてのデータベースでは、upsertメソッドの２番目の引数のカラムに「プライマリ」または「ユニーク」インデックスが必要です。また、MySQLデータベースドライバは、upertメソッドの第２引数を無視し、常にテーブルの「プライマリ」および「ユニーク」インデックスを使用して既存のレコードを検出します。

UPDATE文

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

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

UpdateかInsert

データベース内の既存のレコードを更新、もしくは一致するレコードが存在しない場合は作成したい場合もあるでしょう。このシナリオでは、updateOrInsertメソッドを使用できます。updateOrInsertメソッドは、２つの引数を受け入れます。レコードを検索するための条件の配列と、更新するカラムを示すカラムと値のペアの配列です。

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

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

JSONカラムの更新

JSONカラムを更新する場合は、JSON オブジェクトの適切なキーを更新するために、-> 構文を使用する必要があります。この操作はMySQL5.7以上、およびPostgreSQL9.5以上でサポートします。

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

増分と減分

クエリビルダは、特定のカラムの値を増分または減分するための便利なメソッドも提供します。これらのメソッドは両方とも、少なくとも1つの引数(変更するカラム)を受け入れます。カラムを増分／減分する量を指定するために、２番目の引数を指定できます。

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メソッドは影響を受けた行数を返します。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メソッドを使用することもできます。「更新用」ロックは、選択したレコードが変更されたり、別の共有ロックで選択されたりするのを防ぎます。

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