Laravel クエリビルダ記法まとめ

get()

条件指定に沿った結果データの全てを取得します。

$data = $users->get();

取得出来る結果データは、 StdClass オブジェクトのインスタンスを結果として含む Illuminate\Support\Collection オブジェクトとして返ってきます。個々の結果データにアクセスするには、以下のようにループで回したり、オブジェクトから直接取得する事もできます。

// ループで1件ずつ取得
foreach ($data as $d) {
    echo $d->name;
}

// 結果データそのものから直接取得
echo $data[0]->name;

first()

結果データの最初の1件のみを取得します。結果が何件であっても、1件のみを取得します。

$data = $users->first();

結果データはStdClassオブジェクトで取得されます。取り出すにはオブジェクトのプロパティにアクセスします。

echo $data->name;

value()

value()メソッドで指定した1つのカラムのみを取得します。

$data = $users->value('email');

ただしこのメソッドは条件に限らず1件のみを取得するので、where句などと併用して狙った1件を取得する際などに使います。

echo $data; // test01@test.com

pluck()

pluck()メソッドで指定カラム1つだけをコレクションで取得できます。

$data = $users->pluck('email');

こちらはvalue()メソッドとは違い、条件の結果全てのデータを取得します。

Array
(
    [0] => test01@test.com
    [1] => test03@test.com
    [2] => test04@test.com
    [3] => test05@test.com
    [4] => test06@test.com
    [5] => test07@test.com
    [6] => test08@test.com
    [7] => test09@test.com
    [8] => test10@test.com
)

データへのアクセスは、ループでも直接でも取り出しが可能です。

// ループで1件ずつ取得
foreach ($data as $d) {
    echo $d;
}

// 結果データそのものからアクセス
echo $data[0];

ちなみに、取得コレクションの識別子（プロパティ）カラムを指定する事も可能です。

$data = $users->pluck('email', 'name');

第一引数に取り出すカラムを、第二引数にプロパティカラムを指定する事で。key-valueの形での取得が行えます。

Array
(
    [user01] => test01@test.com
    [user02] => test02@test.com
    [user03] => test03@test.com
    [user04] => test04@test.com
    [user05] => test05@test.com
    [user06] => test06@test.com
    [user07] => test07@test.com
    [user08] => test08@test.com
    [user09] => test09@test.com
    [user10] => test10@test.com
)

exists()

exists()メソッドを使うと、条件に対してレコードが存在するかを確認できます。

$data = $users->exists();

レコードが存在する場合は true を返し、レコードが存在しなければ false が返ります。

if ($data) {
    // レコードは存在する
} elseif (!$data) {
    // レコードは存在しない
}

doesntExist()

doesntExist()メソッドはexists()メソッドの逆です。レコードが存在しない場合は true を返し、レコードが存在する場合は false が返ります。

$data = $users->doesntExist();

if ($data) {
    // レコードは存在しない
} elseif (!$data) {
    // レコードは存在する
}

toSql()

toSql() メソッドで、発行しようとしているSQL文を確認できます。

$data = $users->toSql();

結果を格納した変数 $data に SQL 文が格納されます。

echo $data;  // => select * from `users`

count()

count()メソッドで、レコードの件数を取得できます。

$data = $users->count();

echo $data;  // => 10（件）

max()

max()メソッドで、指定カラムの最大値を取得できます。

$data = $users->max('id');

echo $data;  // => 10

引数にはカラム名を指定します。

min()

min()メソッドで、指定カラムの最小値を取得できます。

$data = $users->min('id');

echo $data;  // => 1

引数にはカラム名を指定します。

avg()

avg()メソッドで、指定カラムの平均値を取得できます。

$data = $users->avg('id');

echo $data;  // => 5.5000

引数にはカラム名を指定します。

sum()

sum()メソッドで、指定カラムの合計値を取得できます。

$data = $users->sum('id');

echo $data;  // => 55

引数にはカラム名を指定します。

rawメソッド

DB::rawは最も基本的なSQL文挿入メソッドです。更に各セクション毎に用意されているrawメソッドを使う事によって、記述を短縮できます。

$data = $users->selectRaw('COUNT(*) AS user_count')->get();

$data = $users->whereRaw('`role` = 1')->get();

$data = $users->whereRaw('`role` = ? AND id < ?', [1, 5])->get();

// 作成日時ごとのユーザー数を求める
$data = $users
        ->selectRaw('count(id) as `count`, created_at')
        ->groupByRaw('created_at')
        ->get();
// => Illuminate\Support\Collection Object
//(
//    [items:protected] => Array
//        (
//            [0] => stdClass Object
//                (
//                    [count] => 86
//                    [created_at] => 2020-06-13 23:07:31
//                )
//
//            [1] => stdClass Object
//                (
//                    [count] => 292
//                    [created_at] => 2020-06-13 23:07:32
//                )
//            .
//            .
//            .
//        )
//)

$data = $users
    ->select('role', DB::raw('SUM(role) as role_count'))
    ->groupBy('role')
    ->havingRaw('role > 1')
    ->get();

// 取得結果
[0] => stdClass Object
    (
        [role] => 5
        [role_count] => 20
    )

[1] => stdClass Object
    (
        [role] => 10
        [role_count] => 50
    )

$data = $users
    ->orderByRaw('updated_at - created_at DESC')
    ->get();

INNER JOIN

join()メソッドを使います。

$data = $users
    ->join('role', 'users.role', '=', 'role.role_id')
    ->join('message', 'users.id', '=', 'message.user_id')
    ->get();

上記のようにjoin()メソッドをチェインする事で、複数のテーブルを結合できます。

LEFT JOIN

leftJoin()メソッドを使います。

$data = $users
    ->leftJoin('message', 'users.id', '=', 'message.user_id')
    ->get();

RIGHT JOIN

rightJoin()メソッドを使います。

$data = $users
    ->rightJoin('message', 'users.id', '=', 'message.user_id')
    ->get();

CROSS JOIN

crossJoin()メソッドを使います。

クロス結合を行いたいテーブルを指定する事で、基テーブルと指定テーブルのデカルト積（すべての行の組み合わせ）を取得できます。

$data = $users->crossJoin('role')->get();

複雑なJOIN

JOINの条件が通常よりも複雑な場合は、第二引数にクロージャを指定し定義する事で、実現できます。

$data = $users
    ->join('contacts', function ($join) {
        $join->on('users.id', '=', 'contacts.user_id')
             ->orOn(...);
    })
    ->get();

クロージャの中でWHERE句を用いることもできます。

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

サブクエリをJOIN

サブクエリをクエリへJOINする為に、メソッド joinSub() leftJoinSub() rightJoinSub() を使用できます。

// サブクエリ
$roles = DB::table('mst_role')->select('id', 'name')->where('deleted_at', null);

// サブクエリをJOINします
$users = DB::table('users')
            ->select('users.id', 'users.name', 'roles.name AS role')
            ->joinSub($roles, 'roles', function ($join) {
                $join->on('users.role', '=', 'roles.id');
            })->get();

print_r($users);
// [0] => stdClass Object
//        (
//            [id] => 1
//            [name] => user01
//            [role] => システム管理者
//        )
//
// [1] => stdClass Object
//        (
//            [id] => 2
//            [name] => user02
//            [role] => 管理者
//        )
//
// [2] => stdClass Object
//        (
//             [id] => 3
//             [name] => user03
//             [role] => ゲスト
//        )

第一引数にサブクエリオブジェクト、第二引数にはそれらを示す任意の識別子、第三引数にクロージャでJOINさせる為の条件式を記述します。

ベーシックなWHERE節

ポピュラーなWHERE節を使うには、where()メソッドを使います。

第一引数にカラム名、第二引数に比較演算子、またはSQLで使うオペレータ、第三引数に比較する値を指定します。

$data = $users->where('id', '>', 5)->get();

第二引数がイコール、つまり等しい場合に限っては、比較演算子を省略し、引数は2つで取得できます。

// id=5 の場合
$data = $users->where('id', 5)->get();

LIKE句も、同様に三つの引数で取得できます。

$data = $users->where('email', 'like', '%test.com')->get();

これらのwhere()メソッドはチェインによって AND でのWHERE句を形成できますが、配列で渡す事によって一度のwhere()メソッドでそれを実現できます。

$data = $users->where([
    ['role', '>', 5],
    ['id', '>', 6]
])->get();

OR節

先に紹介したものはすべて、チェインでつなげるとANDでのWHERE句になりますが、当然、ORも記述できます。

その場合は、orWhere()メソッドを使います。

$data = $users
    ->where('role', '>', 5)
    ->orWhere('role', '>', 8)
    ->get();

whereBetween / orWhereBetween

whereBetween()メソッドを使うと、BETWEEN節（AとBの間）を記述できます。

$data = $users->whereBetween('id', [4, 6])->get();

第一引数に対象カラム、第二引数には配列でそれぞれ、対象の値を渡します。

whereNotBetween / orWhereNotBetween

whereNotBetween()メソッドはwhereBetween()の逆、NOT BETWEEN節（AとBの間ではない）を記述できます。

$data = $users->whereNotBetween('id', [4, 6])->get();

第一引数に対象カラム、第二引数には配列でそれぞれ、対象の値を渡します。

IN節

IN句に関するメソッドは以下の通りです。

$data = $users->whereIn('id', [2, 5, 9])->get();

$data = $users->whereNotIn('id', [2, 5, 9])->get();

「含まれる・含まれない」は完全一致である点に注意してください。”あいまい”の意ではありません。

Null判別

null判定のメソッドは以下の通りです。

$data = $users->whereNull('email')->get();

$data = $users->whereNotNull('email')->get();

日時比較

日時の比較を行う際にも、Laravelのクエリビルダには専用のメソッドが用意されています。

$data = $users->whereDate('updated_at', '2018-03-21')->get();

$data = $users->whereMonth('updated_at', 2)->get();

$data = $users->whereDay('updated_at', 25)->get();

$data = $users->whereYear('updated_at', 2018)->get();

$data = $users->whereTime('updated_at', '<', '12:00')->get();

この辺りの検索には自由度が結構ある（時間だけとか日付の値だけはあまり検索しないので複合的な使い方をする必要もある）ので使い道を考える必要がありますが、通常ではDATE_FORMAT()しなければいけないところをこうしてメソッド一つで実現できてしまうのはとても便利です。

whereColumn / orWhereColumn

whereColumn() メソッドを使うと二つのカラムが同じ値である事を確認できます。

$data = $users->whereColumn('last_login_at', 'updated_at')->get();

このメソッドの素晴らしいところは、同値判定だけでなく、比較演算子も使える事です。

$data = $users->whereColumn('last_login_at', '>', 'updated_at')->get();

そしてそれらは、配列で渡す事で複数設定できます。

$data = $users->whereColumn([
    ['last_login_at', '>', 'updated_at'],
    ['created_at', '<', 'updated_at']
])->get();

ANDやORを()でまとめるWHERE節

少し複雑なWHERE節を組み立てようと思うと、
AND( xxx OR xxx)　や OR( xxx AND xxx)
のようなSQL文も組み立てたい状況があります。

LaravelのクエリビルダはこういったSQL文の組み立てにももちろん対応しています。

where()メソッド、もしくはorWhere()メソッドの中で、クロージャを定義します。

$data = $users
    ->where('role', 5)
    ->orWhere(function ($query) {
        $query->where('gender', 1)
              ->where('age', '>', 20);
    })
    ->get();

// => SELECT * FROM `users` WHERE `role` = 5 OR (`gender` = 1 AND `age` > 20)

Where Exists節

相関副問合せ（副問合せ側から主問合せの列を参照する）にも対応しています。

whereExists()メソッドの中でクロージャを定義し、その中で必要な条件を記述します。

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

// => SELECT * FROM `users` WHERE EXISTS (select 1 from `orders` where orders.user_id = users.id)

サブクエリのWHERE節

サブクエリの結果で条件を指定したい時は、クロージャを利用して記述します。

where() メソッドの中でクロージャを定義し、その中で必要な条件を記述します。以下の例は、 user が hasMany で stage を持っている場合に、ユーザーそれぞれ現在の stage をサブクエリで取得し、それが指定した値（ここでは second）であるかで絞っています。

// 現在のステージプログラムが second のユーザーを取得
$data = $users->where(function ($query) {
            $query->select('stage')
                ->from('stages')
                ->whereColumn('user_id', 'users.id')
                ->orderByDesc('created_at')
                ->limit(1);
        }, 'second')
        ->get();

JSONカラムのWHERE節

使っているDBがMySQL5.7以上、ないしPostgreSQL・SQL Server2016・SQLite3.9.0であれば、JSONカラムに対してのクエリにも対応しています。

使用するメソッドは通常のwhere()やorWhere()などでOKですが、アロー演算子で指定しているところが特徴です。

$data = $users
    ->where('options->language', 'ja')
    ->where('preferences->dining->meal', 'salad')
    ->get();

$data = $users
    ->whereJsonContains('options->language', 'ja')
    ->get();

// 複数の値を指定する
$data = $users
    ->whereJsonContains('options->language', ['ja', 'en'])
    ->get();

$data = $users
    ->whereJsonLength('team->user', 5)
    ->get();

$data = $users
    ->whereJsonLength('team->user', '>', 5)
    ->get();

[{
  "team": {
    "user": [
      {"name": "user01"},
      {"name": "user02"},
      {"name": "user03"},
      {"name": "user04"},
      {"name": "user05"},
    ]
  }
 },{
  "team": {
    "user": [
      {"name": "user001"},
      {"name": "user002"},
      {"name": "user003"},
    ]
  }
}]

単純ソート

単純なソートに関しては、専用のソートメソッドが用意されています。

$data = $users->latest()->get();

$data = $users->oldest()->get();

ランダムで並び替え

inRandomOrder() メソッドを使うと、結果データをランダムな順番にできます。

$data = $users->inRandomOrder()->get();

既存のソートを削除する

reorder() メソッドを使うと、既にセットしたソート順を削除することができます。

// name 昇順 でソート
$users->orderBy('name');

// ソートを解除して取得（name 昇順が解除される）
$data = $users->reorder()->get();

ソート順の削除を行いつつ、新たにソートを設定する場合は、reorder() メソッドに引数を渡します。

// name 昇順 でソート
$users->orderBy('name');

// ソートを解除して新たに設定し取得（name のソートが解除され、email 降順でのソートをセットする）
$data = $users->reorder('email', 'desc')->get();

LIMIT

取得レコード数を制限するには、limit()メソッドを使います。

$data = $users->limit(3)->get();

OFFSET

結果レコードの取得開始位置（何件目から取得するか）を指定するには、offset()メソッドを使います。

$data = $users
    ->offset(2)
    ->limit(3)
    ->get();

OFFSETは得てしてLIMIT節と組み合わせて使う事が多いですが、上記の例の場合では「2件目から3件取得する」という意味になります。

挿入後に自動増分IDを取得する

テーブルが自動増分ID（オートインクリメント＝AUTO_INCREMENT）を持っている場合には、insertGetId()メソッドでレコード挿入を行う事で、返り値に自動増分IDを取得できます。

$data = $users
    ->insertGetId(
        [
            'name' => 'test11',
            'email' => 'test11@test.com',
            'password' => '123456',
            'role' => 5
        ]
    );

注意点として、PostgreSQLでinsertGetId()メソッドを使う場合に、デフォルトでは自動増分IDカラム名は idでなければなりません。

自動増分IDカラム名がidではなく他の名前の場合は、第二引数にそのカラム名を指定する必要があります。

JSONカラムの更新

もちろんJSONカラムも更新が可能です。その場合はアロー演算子（->）で適切な変更対象を指定します。

$data = $users
    ->where('id', 1)
    ->update(['options->enabled' => true]);

カラムの値を増減させる単純な更新

例えば、カラムの中に何かのカウント数を持っていたとして、何かの時にその値を1だけ増やしたいとか減らしたいとか、もしくは任意の数だけ増減させたいなんて事もよくあります。

その時にはincrement()とdecrement()メソッドを使うと簡単に実装する事ができます。

$data = $users->where('id', 1)->increment('count');
$data = $users->where('id', 1)->increment('count', 3);

上記はincrement()メソッドでcountカラムの値を加算させています。

第二引数に値を指定すればその分の数が、指定しなければ1が加算され更新されます。

$data = $users->where('id', 1)->decrement('count');
$data = $users->where('id', 1)->decrement('count', 3);

上記はdecrement()メソッドでcountカラムの値を減算させています。

こちらも同様に、第二引数に値を指定すればその分の数が、指定しなければ1が減算され更新されます。

ちなみに上記は1件のレコードに対して更新を行っていますが、where節を変更して対象のレコードに対してまとめて更新をかける事ももちろん可能です。

そしてincrement()とdecrement()メソッドの使えるところは、増減させるだけではなく、他の通常の更新情報を配列で第三引数に渡す事で、増減以外の更新も行える事です。

$data = $users->where('id', 1)->increment('count', 3, ['name' => 'test1-2']);

上記のようにする事で、増減をおこないつつ、他のカラムに対しても通常の更新を行う事ができます。

TRUNCATE

delete()メソッドは単純にレコードを削除するだけなので、もし全レコードを削除しても、自動増分IDはリセットされません。

もしテーブル内を全て削除し、オートインクリメントもリセットしたい場合は、truncate()メソッドを使います。

$users->truncate();

共有ロック

sharedLock()メソッドを使う事で、トランザクションがコミットされるまでSELECTしている行が更新されることを防ぐことができます。

$data = $users->where('id', 1)->sharedLock()->get();

占有ロック

lockForUpdate()メソッドを使う事で、レコードを更新したりSELECTするために他の共有ロックが行われるのを防ぐことができます。

$data = $users->where('id', 1)->lockForUpdate()->get();