データベース: クエリビルダ (Database: Query Builder)

• Introduction
• データベースクエリの実行
  • チャンク化の結果
  • 結果を遅延的にストリーミングする
  • Aggregates
• 選択ステートメント
• 生の式
• Joins
• Unions
• 基本的な Where 句
  • Where句
  • または Where 句
  • Where Not句
  • Where Any / All / None 句
  • JSON Where句
  • 追加の Where 句
  • 論理的なグループ化
• 高度な Where 句
  • Where Exists 条項
  • サブクエリの Where 句
  • 全文 Where 句
• 順序付け、グループ化、制限およびオフセット
  • Ordering
  • Grouping
  • リミットとオフセット
• 条件節
• ステートメントの挿入
  • Upserts
• 更新ステートメント
  • JSON列の更新
  • インクリメントとデクリメント
• ステートメントの削除
• 悲観的ロック
• Debugging

導入 (Introduction)

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

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

PDO は列名のバインドをサポートしていません。したがって、「order by」列を含め、クエリで参照される列名をユーザー入力によって決定することを決して許可しないでください。

データベースクエリの実行 (Running Database Queries)

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

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 コレクションの詳細については、コレクションのドキュメント をチェックしてください。

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

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

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

return $user->email;

データベーステーブルから単一行を取得したいが、一致する行が見つからない場合は Illuminate\Database\RecordNotFoundException をスローする場合は、firstOrFail メソッドを使用できます。 RecordNotFoundException が捕捉されない場合、404 HTTP 応答が自動的にクライアントに返されます。

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

行全体が必要ない場合は、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 メソッドに 2 番目の引数を指定することで、結果のコレクションがキーとして使用する列を指定できます。

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

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

チャンク化の結果

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

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

chunkById メソッドと lazyById メソッドは、実行されるクエリに独自の「where」条件を追加するため、通常はクロージャ内で独自の条件を 論理的にグループ化する する必要があります。

DB::table('users')->where(function ($query) {
    $query->where('credits', 1)->orWhere('credits', 2);
})->chunkById(100, function (Collection $users) {
    foreach ($users as $user) {
        DB::table('users')
            ->where('id', $user->id)
            ->update(['credits' => 3]);
    }
});

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

結果を遅延的にストリーミングする

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

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

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

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

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

生の式 (Raw Expressions)

場合によっては、クエリに任意の文字列を挿入する必要があるかもしれません。生の文字列式を作成するには、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(/* ... */)) の代わりに使用できます。このメソッドは、オプションのバインディングの配列を 2 番目の引数として受け入れます。

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

whereRaw / orWhereRaw

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

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

内部結合句

クエリビルダは、クエリに結合句を追加するために使用することもできます。基本的な「内部結合」を実行するには、クエリビルダ インスタンスで 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();

左結合/右結合節

「内部結合」の代わりに「左結合」または「右結合」を実行したい場合は、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();

相互結合節

crossJoin メソッドを使用して「クロス結合」を実行できます。クロス結合では、最初のテーブルと結合されたテーブルの間にデカルト積が生成されます。

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

高度な結合句

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

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 行 (最新のブログ投稿ごとに 1 行) を生成できます。結合条件は、サブクエリ内の 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();

労働組合 (Unions)

クエリビルダは、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 句 (Basic Where Clauses)

Where句

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

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

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

便宜上、列が特定の値に対して = であることを確認したい場合は、その値を 2 番目の引数として 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 メソッドに渡される 3 つの引数を含む配列である必要があります。

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

PDO は列名のバインドをサポートしていません。したがって、「order by」列を含め、クエリで参照される列名をユーザー入力によって決定することを決して許可しないでください。

MySQL と MariaDB は、文字列と数値の比較において、文字列を整数に自動的に型キャストします。このプロセスでは、数値以外の文字列が 0 に変換されるため、予期しない結果が生じる可能性があります。たとえば、テーブルに aaa の値を持つ secret 列があり、User::where('secret', 0) を実行すると、その行が返されます。これを回避するには、クエリで使用する前に、すべての値が適切な型に型キャストされていることを確認してください。

または 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句

whereNot メソッドと orWhereNot メソッドは、クエリ制約の特定のグループを無効にするために使用できます。たとえば、次のクエリでは、在庫処分中の製品や価格が 10 未満の製品が除外されます。

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

Where Any / All / None 句

場合によっては、同じクエリ制約を複数の列に適用する必要がある場合があります。たとえば、指定されたリスト内の列が指定された値 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%'
)

whereNone メソッドは、指定された列が指定された制約に一致しないレコードを取得するために使用できます。

$posts = DB::table('albums')
    ->where('published', true)
    ->whereNone([
        'title',
        'lyrics',
        'tags',
    ], 'like', '%explicit%')
    ->get();

上記のクエリの結果は次の SQL になります。

SELECT *
FROM albums
WHERE published = true AND NOT (
    title LIKE '%explicit%' OR
    lyrics LIKE '%explicit%' OR
    tags LIKE '%explicit%'
)

JSON Where句

Laravel は、JSON 列タイプのサポートを提供するデータベースでの JSON 列タイプのクエリもサポートしています。現在、これには MariaDB 10.3 以降、MySQL 8.0 以降、PostgreSQL 12.0 以降、SQL Server 2017 以降、および SQLite 3.39.0 以降が含まれます。 JSON 列をクエリするには、-> 演算子を使用します。

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

whereJsonContains を使用して JSON 配列をクエリできます。

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

アプリケーションが MariaDB、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 句

**どこのような場所 / またはどこのような場所 / どこのような場所ではない / またはどこのような場所ではない **

whereLike メソッドを使用すると、パターン マッチングのためにクエリに「LIKE」句を追加できます。これらのメソッドは、大文字と小文字の区別を切り替える機能を備えた、データベースに依存しない文字列一致クエリの実行方法を提供します。デフォルトでは、文字列の照合では大文字と小文字が区別されません。

$users = DB::table('users')
    ->whereLike('name', '%John%')
    ->get();

caseSensitive 引数を使用して、大文字と小文字を区別した検索を有効にできます。

$users = DB::table('users')
    ->whereLike('name', '%John%', caseSensitive: true)
    ->get();

orWhereLike メソッドを使用すると、LIKE 条件を含む「or」句を追加できます。

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

whereNotLike メソッドを使用すると、クエリに「NOT LIKE」句を追加できます。

$users = DB::table('users')
    ->whereNotLike('name', '%John%')
    ->get();

同様に、orWhereNotLike を使用して、NOT LIKE 条件を含む「or」句を追加できます。

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

whereLike 大文字と小文字を区別する検索オプションは、現在 SQL Server ではサポートされていません。

**どこで / どこでではない / またはどこでで / またはどこでではない **

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 メソッドを使用すると、メモリ使用量を大幅に削減できます。

どこの間/またはどこの間

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

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

whereNotBetween / または WhereNotBetween

whereNotBetween メソッドは、列の値が次の 2 つの値の範囲外にあるかどうかを検証します。

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

whereBetweenColumns / whereNotBetweenColumns / orWhereBetweenColumns / orWhereNotBetweenColumns

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

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

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

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

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 メソッドは、列の値を日付と比較するために使用できます。

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

**過去の場所 / 未来の場所 / 今日の場所 / 今日の場所 / 今日の場所 / 今日の場所 **

wherePast メソッドと whereFuture メソッドは、列の値が過去のものであるか未来のものであるかを判断するために使用できます。

$invoices = DB::table('invoices')
    ->wherePast('due_at')
    ->get();

$invoices = DB::table('invoices')
    ->whereFuture('due_at')
    ->get();

whereNowOrPast メソッドと whereNowOrFuture メソッドは、現在の日付と時刻を含め、列の値が過去のものであるか未来のものであるかを判断するために使用できます。

$invoices = DB::table('invoices')
    ->whereNowOrPast('due_at')
    ->get();

$invoices = DB::table('invoices')
    ->whereNowOrFuture('due_at')
    ->get();

whereToday、whereBeforeToday、および whereAfterToday メソッドは、それぞれ列の値が今日、今日より前、または今日以降であるかどうかを判断するために使用できます。

$invoices = DB::table('invoices')
    ->whereToday('due_at')
    ->get();

$invoices = DB::table('invoices')
    ->whereBeforeToday('due_at')
    ->get();

$invoices = DB::table('invoices')
    ->whereAfterToday('due_at')
    ->get();

同様に、whereTodayOrBefore メソッドと whereTodayOrAfter メソッドを使用して、列の値が今日より前であるか今日以降であるかを判断できます (今日の日付も含みます)。

$invoices = DB::table('invoices')
    ->whereTodayOrBefore('due_at')
    ->get();

$invoices = DB::table('invoices')
    ->whereTodayOrAfter('due_at')
    ->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 句 (Advanced Where Clauses)

Where Exists 条項

whereExists メソッドを使用すると、「存在する場所」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 メソッドに渡します。たとえば、次のクエリは、特定のタイプの最近の「メンバーシップ」を持つすべてのユーザーを取得します。

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 句は現在、MariaDB、MySQL、PostgreSQL でサポートされています。

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

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

順序付け、グループ化、制限およびオフセット (Ordering, Grouping, Limit and Offset)

注文

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

既存の「order by」句をすべて削除し、まったく新しい順序をクエリに適用するために、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();

条件節 (Conditional Clauses)

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

$role = $request->input('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 Statements)

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

DB::table('users')->insert([
    'email' => '[email protected]',
    'votes' => 0
]);

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

DB::table('users')->insert([
    ['email' => '[email protected]', 'votes' => 0],
    ['email' => '[email protected]', 'votes' => 0],
]);

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

DB::table('users')->insertOrIgnore([
    ['id' => 1, 'email' => '[email protected]'],
    ['id' => 2, 'email' => '[email protected]'],
]);

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' => '[email protected]', 'votes' => 0]
);

PostgreSQL を使用する場合、insertGetId メソッドは、自動インクリメント列の名前が id であることを想定します。別の「シーケンス」から ID を取得したい場合は、列名を 2 番目のパラメーターとして insertGetId メソッドに渡すことができます。

アップサート

upsert メソッドは、存在しないレコードを挿入し、指定した新しい値で既存のレコードを更新します。メソッドの最初の引数は挿入または更新する値で構成され、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 番目の引数の列に「プライマリ」または「一意」インデックスが必要です。さらに、MariaDB および MySQL データベース ドライバは、upsert メソッドの 2 番目の引数を無視し、常にテーブルの「プライマリ」インデックスと「一意」インデックスを使用して既存のレコードを検出します。

更新ステートメント (Update Statements)

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

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

更新または挿入

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

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

DB::table('users')
    ->updateOrInsert(
        ['email' => '[email protected]', '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 オブジェクト内の適切なキーを更新する必要があります。この操作は、MariaDB 10.3 以降、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 Statements)

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

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

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

悲観的ロック (Pessimistic Locking)

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

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

あるいは、lockForUpdate メソッドを使用することもできます。 「更新用」ロックは、選択されたレコードが変更されたり、別の共有ロックで選択されたりすることを防ぎます。

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

必須ではありませんが、transaction 内で悲観的ロックをラップすることをお勧めします。これにより、操作全体が完了するまで、取得されたデータがデータベース内で変更されないことが保証されます。失敗した場合、トランザクションは変更をロールバックし、ロックを自動的に解放します。

DB::transaction(function () {
    $sender = DB::table('users')
        ->lockForUpdate()
        ->find(1);

    $receiver = DB::table('users')
        ->lockForUpdate()
        ->find(2);

    if ($sender->balance < 100) {
        throw new RuntimeException('Balance too low.');
    }
    
    DB::table('users')
        ->where('id', $sender->id)
        ->update([
            'balance' => $sender->balance - 100
        ]);

    DB::table('users')
        ->where('id', $receiver->id)
        ->update([
            'balance' => $receiver->balance + 100
        ]);
});

デバッグ (Debugging)

クエリの構築中に 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();