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

• はじめに
• データベースクエリの発行
  • 結果のチャンク化
  • 遅延ストリーミング結果
  • 集計
• SELECT文
• 生の式
• 結合
• ユニオン
• 基本的なWHERE句
  • WHERE句
  • OR WHERE句
  • WHERE NOT句
  • WHERE ANY / ALL / NONE句
  • JSON WHERE句
  • 追加のWHERE句
  • 論理グルーピング
• 高度なWHERE句
  • WHERE EXISTS句
  • サブクエリWHERE句
  • 全文検索WHERE句
• 順序付け、グループ化、制限、オフセット
  • 順序付け
  • グループ化
  • 制限とオフセット
• 条件付き句
• INSERT文
  • アップサート
• UPDATE文
  • JSONカラムの更新
  • 増分と減分
• DELETE文
• 悲観的ロック
• デバッグ

はじめに

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

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

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

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

`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;
}

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

データベーステーブルから単一の行を取得するだけの場合は、`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文

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

生の式

クエリに任意の文字列を挿入する必要がある場合があります。生の文字列式を作成するには、`DB`ファサードによって提供される`raw`メソッドを使用できます

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

生のメソッド

`DB::raw`メソッドを使用する代わりに、次のメソッドを使用して、クエリのさまざまな部分に生の式を挿入することもできます。**生の式を使用するクエリは、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();

結合

内部結合句

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

高度な結合句

より高度な結合句を指定することもできます。開始するには、クロージャを join メソッドの2番目の引数として渡します。クロージャは、"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();

ラテラル結合

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

ユニオン

クエリビルダは、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();

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)

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

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

$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 / orWhereLike / whereNotLike / orWhereNotLike

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

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
)

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つの列の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 / 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" 句をグループ化する必要がある場合がありま

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

高度な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メソッドに渡すことで実現できます。たとえば、次のクエリは、特定の種類の最近の「メンバーシップ」を持つすべてのユーザーを取得します。

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句

whereFullTextおよびorWhereFullTextメソッドは、全文インデックスを持つ列に対して、全文検索の「where」句をクエリに追加するために使用できます。これらのメソッドは、Laravelによって基盤となるデータベースシステムに適切なSQLに変換されます。たとえば、MariaDBまたは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メソッドを呼び出すときに列と方向を渡して、既存のすべての「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 (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メソッドも提供します。 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]
);

アップサート

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列を更新します。

UPDATE文

データベースにレコードを挿入することに加えて、クエリビルダは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文

クエリビルダの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();

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

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