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

• はじめに
• データベースクエリの実行
  • 結果のチャンク処理
  • 結果の遅延ストリーミング
  • 集約
• SELECT文
• 生の式
• 結合（Joins）
• UNION
• 基本的なWHERE句
  • WHERE句
  • OR WHERE句
  • WHERE NOT句
  • WHERE ANY / ALL句
  • JSON WHERE句
  • 追加のWHERE句
  • 論理的グルーピング
• 高度なWHERE句
  • WHERE EXISTS句
  • サブクエリWHERE句
  • 全文検索WHERE句
• 並び替え、グルーピング、制限とオフセット
  • 並び替え
  • グルーピング
  • 制限とオフセット
• 条件付き句
• 挿入文
  • アップサート
• 更新文
  • JSONカラムの更新
  • インクリメントとデクリメント
• 削除文
• 悲観的ロック
• デバッグ

#はじめに

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
{
    /**
     * アプリケーションのすべてのユーザー一覧を表示します。
     */
    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;

行全体が不要で、特定のカラムの値だけを取得したい場合は、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) {
    // レコードを処理...

    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句の指定

テーブルのすべてのカラムを選択したくない場合があります。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インジェクションから完全に保護されている保証はLaravelにはありません。

#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）

#内部結合句

クエリビルダーは、クエリに結合句を追加するためにも使用できます。基本的な「内部結合（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）句

「内部結合」ではなく「左結合」や「右結合」を行いたい場合は、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 メソッドを使って「クロス結合」を行えます。クロス結合は、最初のテーブルと結合するテーブルのデカルト積を生成します：

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

#高度な結合句

より高度な結合句を指定することも可能です。まず、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();

#ラテラル結合（Lateral Joins）

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 句

#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 の第1引数にクロージャを渡せます：

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

複数のカラムに同じクエリ制約を適用したい場合があります。例えば、指定したカラムのいずれかが特定の値に 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 句

Laravel は、JSON カラム型をサポートするデータベースでのクエリもサポートしています。現在、MySQL 5.7+、PostgreSQL、SQL Server 2016、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
)

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

#高度な 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 条件

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->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引数に別のクロージャを渡すこともできます。これは第1引数が 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' => '[email protected]',
    'votes' => 0
]);

複数のレコードを一度に挿入する場合は、配列の配列を渡します。各配列はテーブルに挿入されるレコードを表します。

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

insertOrIgnore メソッドは、レコード挿入時のエラーを無視します。このメソッドを使う場合、重複レコードのエラーは無視され、データベースエンジンによっては他の種類のエラーも無視される可能性があります。例えば、insertOrIgnore は MySQLのstrictモードをバイパスします。

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 メソッドは、存在しないレコードを挿入し、既に存在するレコードは指定した新しい値で更新します。第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 の値を持つレコードが既に存在する場合、そのレコードの price カラムを更新します。

#更新文

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

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

#更新または挿入

場合によっては、データベース内の既存レコードを更新するか、該当するレコードが存在しない場合は作成したいことがあります。このような場面では、updateOrInsert メソッドを使用できます。updateOrInsert メソッドは2つの引数を受け取ります：レコードを検索する条件の配列と、更新するカラムと値のペアを示す配列です。

updateOrInsert は第1引数の条件でレコードを探し、存在すれば第2引数の値で更新します。見つからなければ、両方の引数の属性をマージして新規レコードを挿入します。

DB::table('users')
    ->updateOrInsert(
        ['email' => '[email protected]', 'name' => 'John'],
        ['votes' => '2']
    );

#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句を追加して制約できます：

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