Laravel クエリビルダ記法まとめ
- 公開日
- 更新日
- カテゴリ：Laravel
- タグ：PHP,Laravel,QueryBuilder
クエリビルダとは、データベースからレコードを取得する際に SQL 文を組み立てて問い合わせを行いますが、それを簡単に組み立てる事の出来る機能です。
SQL 文に詳しくなくても、この機能によって適切な書式で問い合わせを行える為、とても便利な機能です。また、昨今の代表的な PHP フレームワークではおおよそ提供されている機能でもあります。
本記事では Laravel の QueryBuilder について、その機能を入門レベルでもわかるように少しだけ噛み砕いて記載しています。
Contents
- 実行環境
- クエリビルダを使用する為の基本書式
- 結果データ取得
- 分割処理
- 集計
- SELECT
- DISTINCT
- SQL 文を直接記述する
- JOIN
- UNION
- WHERE
- 結果データのソート
- グループ化（GROUP BY）と絞り込み（HAVING）
- 取得レコード数の制限・スキップ
- when 判定式によって検索条件を変更する
- INSERT
- UPDATE
- DELETE
- 排他制御
実行環境
- Laravel 9
- PHP 8.1
クエリビルダを使用する為の基本書式
基本的な書式を以下に示します。
use Illuminate\Support\Facades\DB;
public function hoge()
{
/** @var \Illuminate\Database\Query\Builder $userBuilder */
$userBuilder = DB::table('users'); // テーブル名を指定
}
- DB クラスを use します。
- DB::table() の引数に、取得したいテーブル名を渡します。
今回は例として users テーブルから情報を取得する為のクエリビルダを記述していきますが、users テーブルのビルダインスタンスを格納した変数 $userBuilder に対してクエリを記述し、結果を取得していきます。
これから先は全て、このベースとなる記述の上に、該当のメソッドを記述していく流れになります。
結果データ取得
条件を記述していく前に、結果データの取得を以下に示します。 Laravel のクエリビルダには以下の取得メソッドがあります。
get
条件指定に沿った結果データの全てを取得します。
// データを取得します。
$users = $userBuilder->get();
取得出来る結果データは、StdClass を結果として含む Collection として返ってきます。個々の結果データにアクセスするには、以下のようにループで回したり、オブジェクトから直接取得する事もできます。
// Collection で操作
$userIds = $users->pluck('id');
// foreach で 1 件ずつ取得
foreach ($users as $user) {
echo $user->name;
}
// 結果データそのものから直接取得
echo $users[0]->name;
first
結果データの最初の 1 件のみを取得します。結果が何件であっても、1 件のみを取得します。
$user = $userBuilder->first();
結果データは StdClass オブジェクトで取得されます。取り出すにはオブジェクトのプロパティにアクセスします。
$user->name;
sole
sole メソッドは、 結果データが必ず 1 件であることを保証して取得します。
$user = $userBuilder->sole();
結果データは StdClass オブジェクトで取得されます。
このメソッドの特徴として、「結果データが 0 件」「結果データが複数件」の場合は例外がスローされます。
$user = $userBuilder->sole();
// 結果が 0 件 -> \Illuminate\Database\RecordsNotFoundException
// 結果が複数件 -> \Illuminate\Database\MultipleRecordsFoundException
value
value メソッドで指定した 1 つのカラムのみを取得します。
$userName = $userBuilder->value('name');
ただしこのメソッドは、データが何件あろうが最初にヒットした 1 件の指定カラム値のみを取得します。
echo $userName; // Eli Crist
pluck
pluck メソッドで指定カラム 1 つだけをコレクションで取得できます。
/** @var Collection $userNames */
$userNames = $userBuilder->pluck('name');
こちらは value() メソッドとは違い、結果全ての指定カラム値を取得します。
Illuminate\Support\Collection Object
(
[items:protected] => Array
(
[0] => Jayson Lakin
[1] => Sam McCullough
[2] => Mrs. Heather Green I
[3] => Glenda Frami
[4] => Ofelia Dach
[5] => Prof. Mandy Lakin
[6] => Prof. Ewald Bins
.
.
.
)
データへのアクセスは、ループでも直接でも取り出しが可能です。
// collection each で1件ずつ処理
$userNames->each(function (string $userName) {
echo $userName;
});
// foreach で1件ずつ処理
foreach ($userNames as $userName) {
echo $userName;
}
// 結果データそのものからアクセス
echo $userNames[0];
ちなみに、取得コレクションの識別子（キー）に任意のカラムを指定する事も可能です。
// key: id, value: name として取得
$userNames = $userBuilder->pluck('name', 'id');
第一引数に取り出すカラムを、第二引数にキーとして使いたいカラムを指定する事で、key-value の形での取得が行えます。
Illuminate\Support\Collection Object
(
[items:protected] => Array
(
[1] => Jayson Lakin
[2] => Sam McCullough
[3] => Mrs. Heather Green I
[4] => Glenda Frami
[5] => Ofelia Dach
[6] => Prof. Mandy Lakin
[7] => Prof. Ewald Bins
[8] => Miss Destiny Nicolas
.
.
.
)
exists
exists メソッドを使うと、条件に対してレコードが存在するかを確認できます。
/** @var boolean $existsUsers */
$existsUsers = $userBuilder->exists();
レコードが存在する場合は true を返し、レコードが存在しなければ false が返ります。
if ($existsUsers) {
// レコードは存在する
} else {
// レコードは存在しない
}
doesntExist
doesntExist メソッドは exists() メソッドの逆です。レコードが存在しない場合は true を返し、レコードが存在する場合は false が返ります。
/** @var boolean $doesntExistsUsers */
$doesntExistsUsers = $userBuilder->doesntExist();
if ($doesntExistsUsers) {
// レコードは存在しない
} else {
// レコードは存在する
}
toSql
toSql メソッドでは、クエリビルダで組み立て発行しようとしている SQL 文を確認できます。
/** @var string $sql */
$sql = $userBuilder->toSql();
結果を格納した変数に SQL 文が格納されます。
echo $sql; // => select * from `users`
分割処理
レコードを取得し処理を行おうとした際に、対象レコードが数千・数万件レベルであった場合はメモリの関係などで一度に取得する事は難しいかもしれません。
そんな時は以下の分割処理メソッドを用いて小さなかたまりごとに処理を使えば、取得条件の結果データに対してリソースに優しく処理を行う事ができます。
chunk
$userBuilder->orderBy('id')->chunk(10, function (Collection $chunkedUsers): void {
// 10 件の $chunkedUsers
$chunkedUsers->each(function (object $user) {
//
});
});
上記の例では、users テーブルに対し 10 件ずつレコードを取得し、クロージャの中でそれらを処理しています。
つまり上記例の場合、総件数 100 件のデータなら 10 件ずつ、合計 10 回ループすることになります。
全体件数は 10,000 件だけど 100 件ずつ取り出して処理を行ったりなど、メモリリソースを節約しながら処理を行っていくことができます。
1 点気を付ける事は、chunk メソッドを使用する際は必ず orderBy() でデータの並び順を指定する必要があります。
ちなみに、クロージャ内で false を返すと分割処理を中断することができます。
$userBuilder->orderBy('id')->chunk(10, function (Collection $chunkedUsers) {
echo "chunked!";
return false; // chunk ループを終了させる
});
つまり上記例の場合、データの総件数が 100 件だったとして、10 件ずつ分割するため 10 回ループしますが、return false しているので「chunked!」は 1 度のみ出力され、その後のループは中断されます。
chunkById
並び順がシンプルに Primary Key（主キー）昇順で良い場合は、chunkById() を利用することもできます。
$userBuilder->chunkById(10, function (Collection $chunkedUsers): void {
// 10 件の $chunkedUsers
$chunkedUsers->each(function (object $user) {
//
});
});
主キー名が同一のテーブルを結合している場合
chunkById を使用する際に注意点があります。
テーブルを結合（join）していて、Primary key 名が他テーブルと同一のものがある場合はこれまでの記述では失敗します。
// users テーブルと posts テーブル両方に主キー id カラムがある状態で id カラムによるソートで chunk する
$userBuilder->join('posts', 'users.id', '=', 'posts.user_id')
->chunkById(10, function (Collection $chunkedUsers): void {
//
});
// エラーになる
// SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in order clause is ambiguous (SQL: select * from `users` inner join `posts` on `users`.`id` = `posts`.`user_id` order by `id` asc limit 10)
これは SQL 文を確認するとわかる通り、order by に指定している id カラムがどちらのテーブルの id カラムであるのかが不明なために発生します。
select
*
from `users`
inner join `posts` on `users`.`id` = `posts`.`user_id`
order by `id` asc
limit 10
このような場合は、chunkById() の第三引数に「ソート対象とする主キー（がどちらの主キーであるか）」を、第四引数にエイリアスを指定してあげることで解決できます。
$userBuilder->join('posts', 'users.id', '=', 'posts.user_id')
->chunkById(10, function (Collection $chunkedUsers): void {
//
}, 'users.id', 'id'); // users テーブルの id を ソートの際の id として扱います
https://laravel.com/api/9.x/Illuminate/Database/Concerns/BuildsQueries.html#method_chunkById
chunkMap
chunkMap() では、結果データを map（個別処理して返す）する際に処理を分割します。
// 100 件ずつ分割で map を行った結果データを取得します
/** @var Collection $users */
$users = $userBuilder->orderBy('id')->chunkMap(fn($user): array => [
'id' => $user->id,
'name' => $user->name,
], 100);
Illuminate\Support\Collection Object
(
[items:protected] => Array
(
[0] => Array
(
[id] => 1
[name] => Jayson Lakin
)
[1] => Array
(
[id] => 2
[name] => Sam McCullough
)
[2] => Array
(
[id] => 3
[name] => Mrs. Heather Green I
)
.
.
.
)
取得したい結果データの個々は小さいので全体で 1 つにまとめたいが、元々の個々データが大きく処理自体は分割したい。 例えば、レコード 1 件の中に BLOB 型や TEXT 型などサイズの大きなデータを含んでいるけど、取得したいのはこれらを省く小さなデータのみの場合とか、そういったケースなどで使えるかもしれません。
lazy
lazy メソッドは chunk と同じような動作をしますが、LazyCollection によって内部的に chunk を行ってくれるため、chunk のようにコールバックが不要になります。
$userBuilder->orderBy('id')->lazy()
チャンクサイズのデフォルトは 1000 件です。任意の値にしたい時は引数を渡します。
// chunk size を 5 件に指定
$userBuilder->orderBy('id')->lazy(5)
シンプルに Primary Key で order するのであれば、lazyById, lazyByIdDesc が使えます。
// chunk size を 5 件に指定
$userBuilder->lazyById(5)
chunk か lazy か
処理のかたまりを分割してメモリリソースが節約できればそれで良いのですが、気にはなるので一応同じ条件で動作させてみた結果を残します。
10000 件のレコードに対して 「分割しない」「chunk を使用」「lazy を使用」での実行結果です。
// 分割なし
$userBuilder->get()->each(function (object $user) {
echo $user->id;
});
// 実行時間: 0.157 秒
// メモリ使用量: 22.377 MB
// chunk を使用
$userBuilder->chunkById(1000, function (Collection $chunkedUsers): void {
$chunkedUsers->each(function (object $user) {
echo $user->id;
});
});
// 実行時間: 0.1676 秒
// メモリ使用量: 3.175 MB
// lazy を使用
$userBuilder->lazyById(1000)->each(function (object $user) {
echo $user->id;
});
// 実行時間: 0.234 秒
// メモリ使用量: 5.312 MB
（実行時間については、それぞれ 10 回走らせてもっとも早かった時間を記載）
集計
Laravel のクエリビルダでは、一通りの集計メソッドが準備されています。
count
count メソッドで、レコードの件数を取得できます。
$userBuilder->count();
// => 50（件）
引数にカラム名を指定するもできます。
max
max メソッドで、指定カラムの最大値を取得できます。引数にカラム名を指定します。
$userBuilder->max('age');
// => 60
min
min メソッドで、指定カラムの最小値を取得できます。引数にカラム名を指定します。
$userBuilder->min('age');
// => 21
avg
avg メソッドで、指定カラムの平均値を取得できます。引数にカラム名を指定します。
$userBuilder->avg('age');
// => 39.2600
sum
sum メソッドで、指定カラムの合計値を取得できます。引数にカラム名を指定します。
$userBuilder->sum('age');
// => 1963
SELECT
取得するカラムを指定する為には、select() メソッドを利用します。
/** @var Collection $users */
$users = $userBuilder->select('name', 'email as user_email')->get();
引数に取得したいカラムを渡しますが、上記の通り、エイリアスも書けます。
Illuminate\Support\Collection Object
(
[items:protected] => Array
(
[0] => stdClass Object
(
[name] => Jayson Lakin
[user_email] => rau.jess@example.org
)
[1] => stdClass Object
(
[name] => Sam McCullough
[user_email] => maureen.grimes@example.com
)
.
.
.
)
select() メソッドにて取得メソッドを定義した後でさらに追加で取得カラムを追加する時は、addSelect メソッドで追加できます。
$userBuilder->select('name', 'email as user_email');
// 追加で取得カラムを指定
$userBuilder->addSelect('age');
$users = $userBuilder->get();
Illuminate\Support\Collection Object
(
[items:protected] => Array
(
[0] => stdClass Object
(
[name] => Jayson Lakin
[user_email] => rau.jess@example.org
[age] => 33
)
[1] => stdClass Object
(
[name] => Sam McCullough
[user_email] => maureen.grimes@example.com
[age] => 47
)
.
.
.
)
DISTINCT
distinct メソッドを用いると、重複行を除去した結果を取得できます。
$users = $userBuilder->distinct()->get();
SQL 文を直接記述する
クエリビルダの中で、直接 SQL 文を記述する事もできます。
DB::raw メソッドを使って SQL 文を記述していきます。
$userBuilder->select(DB::raw('COUNT(*) AS user_count'))
raw メソッド
DB::raw は最も基本的な SQL 文挿入メソッドです。更に各セクション毎に用意されている raw メソッドを使う事によって、記述を短縮できます。
selectRaw
selectRaw メソッドは select(DB::raw(...)) 式を置き換えます。
$userBuilder->selectRaw('COUNT(*) AS user_count')->get();
whereRaw / orWhereRaw
whereRaw と orWhereRaw メソッドは、WHERE へ SQL 文を直接挿入できます。
$userBuilder->whereRaw('`age` = 26')->get();
また、第二引数に配列の形で値を渡してあげる事で、パラメーターをバインドできます。
$users = $userBuilder->whereRaw('`age` > ? AND id < ?', [30, 5])->get();
groupByRaw
groupByRaw メソッドは、GROUP BY の値を指定できます。
// 年齢ごとのユーザー数を求める
$userBuilder->selectRaw('count(id) as `count`, age')
->groupByRaw('age')
->get();
Illuminate\Support\Collection Object
(
[items:protected] => Array
(
[0] => stdClass Object
(
[count] => 1
[age] => 33
)
[1] => stdClass Object
(
[count] => 3
[age] => 47
)
[2] => stdClass Object
(
[count] => 2
[age] => 48
)
[3] => stdClass Object
(
[count] => 3
[age] => 27
)
.
.
.
)
)
havingRaw / orHavingRaw
havingRaw と orHavingRaw メソッドは、GROUP BY を行った際に having へ SQL 文を直接挿入できます。
// 都道府県別 30 歳以上のユーザー件数
$userBuilder->select('prefecture_id', 'age', DB::raw('COUNT(age) as number_age'))
->groupBy('prefecture_id', 'age')
->havingRaw('age >= 30')
->get();
Illuminate\Support\Collection Object
(
[items:protected] => Array
(
[0] => stdClass Object
(
[prefecture_id] => 4
[age] => 33
[number_age] => 1
)
[1] => stdClass Object
(
[prefecture_id] => 9
[age] => 47
[number_age] => 1
)
[2] => stdClass Object
(
[prefecture_id] => 15
[age] => 48
[number_age] => 1
)
.
.
.
)
orderByRaw
orderByRaw メソッドは、ORDER BY へ SQL 文を直接挿入できます。
$userBuilder->orderByRaw('created_at DESC')->get();
JOIN
クエリビルダで JOIN を書く場合は、以下のようになります。
INNER JOIN
join メソッドを使います。
$userBuilder->join('posts', 'users.id', '=', 'posts.user_id')
->join('post_tag', 'posts.id', '=', 'post_tag.post_id')
->join('tags', 'post_tag.tag_id', '=', 'tags.id')
上記のように join() メソッドをチェインする事で、複数のテーブルを結合できます。
LEFT JOIN
leftJoin メソッドを使います。
$data = $users
->leftJoin('message', 'users.id', '=', 'message.user_id')
->get();
RIGHT JOIN
rightJoin メソッドを使います。
$userBuilder->leftJoin('posts', 'users.id', '=', 'posts.user_id')
CROSS JOIN
crossJoin メソッドを使います。
クロス結合を行いたいテーブルを指定する事で、基テーブルと指定テーブルのすべての行の組み合わせを取得できます。
$userBuilder->crossJoin('tags')
複雑な JOIN
JOIN の条件が通常よりも複雑な場合は、第二引数にクロージャを指定し定義する事で実現できます。
$userBuilder->join('posts', function (JoinClause $join): void {
$join->on('users.id', '=', 'posts.user_id')
->orOn(...);
})
クロージャの中で WHERE を用いることもできます。
$userBuilder->join('posts', function (JoinClause $join): void {
$join->on('users.id', '=', 'posts.user_id')
->where('posts.created_at', '>', Carbon::now()->subDays(7));
})
サブクエリを JOIN
サブクエリをクエリへ JOIN する為に、メソッド joinSub(), leftJoinSub(), rightJoinSub() を使用できます。
第一引数にサブクエリを定義したビルダー、第二引数にはエイリアス名（任意の識別子）、第三引数にクロージャで JOIN させる為の条件式を記述します。
// サブクエリ
/** @var \Illuminate\Database\Query\Builder $postBuilder */
$postBuilder = DB::table('posts')
->where('created_at', '>', Carbon::now()->subWeek())
->select('id', 'user_id', 'title AS post_title', 'created_at AS date_published');
// サブクエリをJOINします
$users = $userBuilder->joinSub($postBuilder, 'posts', function (JoinClause $join): void {
$join->on('users.id', '=', 'posts.user_id');
})
->select('users.id', 'users.name', 'posts.post_title', 'posts.date_published')
->get();
Illuminate\Support\Collection Object
(
[items:protected] => Array
(
[0] => stdClass Object
(
[id] => 1
[name] => Jayson Lakin
[post_title] => Alice began.
[date_published] => 2022-10-13 13:36:47
)
[1] => stdClass Object
(
[id] => 1
[name] => Jayson Lakin
[post_title] => There was exactly.
[date_published] => 2022-10-13 13:36:47
)
[2] => stdClass Object
(
[id] => 1
[name] => Jayson Lakin
[post_title] => Im doubtful about.
[date_published] => 2022-10-13 13:36:47
)
.
.
.
)
UNION
UNION（クエリ結合）を行う場合は、union() / unionAll メソッドを使います。
$user = DB::table('users')->where('id', '=', 1)
->select('id', 'name');
$users = $userBuilder->where('id', '=', 2)
->union($user)
->select('id', 'name')
->get();
上記のように、クエリ結合を行うビルダーを union() メソッドに渡してあげます。
union を行う場合、個々を別々のビルダーにしてあげるのがポイントです。
WHERE
ここからは条件付与（WHERE）について記載していきます。
ベーシックな WHERE
WHERE を使うには、where メソッドを使います。
第一引数にカラム名、第二引数に比較演算子、または SQL で使うオペレータ、第三引数に比較する値を指定します。
$users = $userBuilder->where('id', '>', 10)
->get();
第二引数がイコール、つまり等しい場合に限っては比較演算子を省略し、引数 2 つで記述することも可能です。
// id=10 の場合
$userBuilder->where('id', 10)
LIKE も、同様に三つの引数で取得できます。
$userBuilder->where('email', 'like', '%.org')
複数の where を記述する場合は where() メソッドをつなげることで AND 条件になります。
$userBuilder->where('email', 'like', '%.org')
->where('id','<', 10)
// select * from `users` where `email` like ? and `id` < ?
配列で渡す事によって一度の where() メソッドで実現することも可能です。（SQL 文としては厳密に同じではないため、目的に応じて選択）
$userBuilder->where([
['email', 'like', '%.org'],
['id','<', 10],
])
// select * from `users` where (`email` like ? and `id` < ?)
OR
OR where を記述する場合は orWhere メソッドを使います。
$userBuilder->where('email', 'like', '%.org')
->orWhere('id','<', 10)
// select * from `users` where `email` like ? or `id` < ?
whereBetween / orWhereBetween
whereBetween メソッドを使うと、BETWEEN（A と B の間）を記述できます。
$userBuilder->whereBetween('id', [4, 6])
// select * from `users` where `id` between 4 and 6
第一引数に対象カラム、第二引数には配列でそれぞれ、対象の値を渡します。
whereNotBetween / orWhereNotBetween
whereNotBetween メソッドは whereBetween() の逆、NOT BETWEEN（A と B の間ではない）を記述できます。
$userBuilder->whereNotBetween('id', [4, 6])
// select * from `users` where `id` not between 4 and 6
第一引数に対象カラム、第二引数には配列でそれぞれ、対象の値を渡します。
whereBetweenColumns / whereNotBetweenColumns / orWhereBetweenColumns / orWhereNotBetweenColumns
whereBetweenColumns メソッドは、第一引数に指定したカラムの値が、第二引数で渡すカラムリストの間にあるかを確認します。
// weight カラムの値が、minimum_allowed_weight カラムの値と maximum_allowed_weight カラムの値の間にあるか
$userBuilder->whereBetweenColumns('weight', [
'minimum_allowed_weight',
'maximum_allowed_weight',
])
// select * from `users` where `weight` between `minimum_allowed_weight` and `maximum_allowed_weight`
IN
IN に関するメソッドは以下の通りです。
whereIn / orWhereIn
whereIn メソッドを使うと、IN（含まれる）を記述できます。
$userBuilder->whereIn('id', [2, 5, 9])
// select * from `users` where `id` in (2, 5, 9)
whereNotIn / orWhereNotIn
whereNotIn メソッドの場合は NOT IN（含まれない）を記述できます。
$userBuilder->whereNotIn('id', [2, 5, 9])
// select * from `users` where `id` not in (2, 5, 9)
「含まれる・含まれない」は個々の値について完全一致である点に注意してください。”あいまい”の意ではありません。
Null 判別
null 判定のメソッドは以下の通りです。
whereNull / orWhereNull
whereNull メソッドを使うと、カラムの値が null である条件を記述できます。
$userBuilder->whereNull('email')
// select * from `users` where `email` is null
whereNotNull / orWhereNotNull
対して、whereNotNull メソッドを使うと、カラムの値が null ではない条件を記述できます。
$userBuilder->whereNotNull('email')
// select * from `users` where `email` is not null
日時比較
日時比較に関しては、Laravel のクエリビルダには専用のメソッドが用意されています。
whereDate / orWhereDate
日付比較には whereDate メソッドを使います。
$userBuilder->whereDate('birth_date', '<', '2017-01-01')
// select * from `users` where date(`birth_date`) < '2017-01-01'
whereMonth / orWhereMonth
月の比較には、whereMonth メソッドを使います。
$userBuilder->whereMonth('birth_date', '=', 1)
// select * from `users` where month(`birth_date`) = 1
whereDay / orWhereDay
日付の値そのものとの比較には whereDay() メソッドを使います。
$userBuilder->whereDay('birth_date', '=', 12)
// select * from `users` where day(`birth_date`) = 12
whereYear / orWhereYear
年の比較には、whereYear メソッドを使います。
$userBuilder->whereYear('birth_date', '=', 2000)
// select * from `users` where year(`birth_date`) = 2000
whereTime / orWhereTime
時間の比較には、whereYear メソッドを使います。
$userBuilder->whereTime('updated_at', '<', '12:00')
// select * from `users` where time(`updated_at`) < '12:00'
whereColumn / orWhereColumn
whereColumn メソッドを使うと二つのカラムを比較できます。
$userBuilder->whereColumn('created_at', '=', 'updated_at')
// select * from `users` where `created_at` = `updated_at`
配列で渡す事で複数設定できます。
$userBuilder->whereColumn([
['email_verified', '>', 'updated_at'],
['created_at', '<', 'updated_at']
])
// select * from `users` where (`email_verified` > `updated_at` and `created_at` < `updated_at`)
AND や OR を() でまとめる WHERE
少し複雑な WHERE AND(xxx OR xxx) や OR(xxx AND xxx) のような SQL 文を組み立てたい時は、where メソッド、もしくは orWhere メソッドの中で、クロージャを定義します。
$userBuilder->where('id', '>', '10')
->orWhere(function (Builder $query) {
$query->where('age', '>', '20')
->where('prefecture_id', '=', 14);
})
// select * from `users` where `id` > 10 or (`age` > 20 and `prefecture_id` = 14)
// こちらでも結果は同じ
$userBuilder->where('id', '>', '10')
->orWhere([
['age', '>', '20'],
['prefecture_id', '=', 14],
])
Where Exists
相関副問合せ（副問合せ側から主問合せの列を参照する）にも対応しています。
whereExists メソッドの中でクロージャを定義し、その中で必要な条件を記述します。
$userBuilder->whereExists(function (Builder $query) {
$query->select(DB::raw(1))
->from('posts')
->whereColumn('posts.user_id', 'users.id');
})
// select * from `users` where exists (select 1 from `posts` where `posts`.`user_id` = `users`.`id`)
サブクエリ WHERE
サブクエリの結果で条件を指定したい時は、クロージャを利用して記述します。
where メソッドの中でクロージャを定義し、その中で必要な条件を記述します。 以下の例では、3 日以内に投稿したユーザーを取得しています。
// 3 日以内に投稿したユーザーを取得
$userBuilder->where(function (Builder $query) {
$query->select('created_at')
->from('posts')
->whereColumn('posts.user_id', 'users.id')
->orderByDesc('posts.created_at')
->limit(1);
}, '>', Carbon::now()->subDays(3))
// select * from `users` where (select `created_at` from `posts` where `posts`.`user_id` = `users`.`id` order by `posts`.`created_at` desc limit 1) > ?
JSON カラムの WHERE
JSON 型のカラムに対してのクエリにも対応しています。
where() などでアロー演算子を使用します。
$userBuilder->where('options->language', 'ja')
->where('preferences->dining->meal', 'salad')
whereJsonContains / orWhereJsonContains
whereJsonContains メソッドは JSON 配列に対するクエリを構築します。
$userBuilder->whereJsonContains('options->language', 'ja')
whereJsonLength / orWhereJsonLength
whereJsonLength メソッドを使う事でデータ長を指定できます。
$userBuilder->whereJsonLength('team->user', 5)
$userBuilder->whereJsonLength('team->user', '>', 5)
※ データ長とは、つまり個数です。上記メソッド例では、「user を５つ（後者は「より多い」）持ったデータ」という意味になります。
[{
"team": {
"user": [
{"name": "user01"},
{"name": "user02"},
{"name": "user03"},
{"name": "user04"},
{"name": "user05"},
]
}
},{
"team": {
"user": [
{"name": "user001"},
{"name": "user002"},
{"name": "user003"},
]
}
}]
結果データのソート
取得するデータの並び順を指定します。
orderBy
orderBy メソッドを使うと、指定したカラムで結果データをソートします。
$$userBuilder->orderBy('id')
// select * from `users` order by `id` asc
$$userBuilder->orderBy('id'. 'desc')
前者の場合、デフォルトで昇順になります。後者のように、第二引数に昇順(asc), もしくは降順(desc) のどちらかを指定することも出来ます。
また、順序を複数指定したい場合はチェインします。
$userBuilder->orderBy('age')
->orderBy('id')
// select * from `users` order by `age` asc, `id` asc
orderByDesc
orderByDesc メソッドで降順にできます。
$userBuilder->orderByDesc('id')
// select * from `users` order by `id` desc
latest
latest メソッドで降順にできます。
デフォルトでは created_at カラムでソートされます。
$userBuilder->latest()
// select * from `users` order by `created_at` desc
引数を渡すことでカラムを指定することもできます。
$userBuilder->latest('id')
// select * from `users` order by `id` desc
oldest
oldest メソッドで昇順にできます。
$userBuilder->oldest()
// select * from `users` order by `created_at` asc
こちらも latest() 同様、デフォルトでは created_at カラムでソートされますが、引数を渡すことでカラムを指定することもできます。
inRandomOrder
inRandomOrder メソッドを使うと、結果データをランダムな順番にできます。
$userBuilder->inRandomOrder()
// select * from `users` order by RAND()
reorder
reorder メソッドを使うと、既にセットしたソート順を削除することができます。
$userBuilder->orderBy('age')
->orderBy('id')
// ソートを解除
$userBuilder->reorder()
// select * from `users`
ソート順の削除を行いつつ、新たにソートを設定する場合は、reorder メソッドに引数を渡します。
$userBuilder->orderBy('age')
->orderBy('id')
// ソートを解除して新たに設定し取得（age, id のソートが解除され、email 降順でのソートをセットする）
$userBuilder->reorder('email', 'desc')->get();
// select * from `users` order by `email` desc
グループ化（GROUP BY）と絞り込み（HAVING）
集計を行う場合は groupBy メソッド、そして having メソッドを使います。
// 都道府県別 20 歳以上のユーザー数を集計します
$userBuilder->groupBy('prefecture_id', 'age')
->having('age', '>', 20)
->orderBy('prefecture_id')
->selectRaw('prefecture_id, count(prefecture_id) as value')
// select prefecture_id, count(prefecture_id) as value from `users` group by `prefecture_id`, `age` having `age` > 20 order by `prefecture_id` asc
取得レコード数の制限・スキップ
条件に対して返ってくる結果レコードの数を制限したり、件数をスキップ（飛ばす）したりもできます。
LIMIT
取得レコード数を制限するには、limit メソッドを使います。
$userBuilder->limit(3)
// select * from `users` limit 3
OFFSET
結果レコードの取得開始位置（何件目から取得するか）を指定するには、offset メソッドを使います。
$userBuilder->offset(2)
->limit(3)
// select * from `users` limit 3 offset 2
limit() と offset() のシノニム（同じもの）として、take() と skip() というメソッドもあります。
$userBuilder->skip(2)
->take(3)
// select * from `users` limit 3 offset 2
skip() は offset() と、take() は limit() と同じです。
when 判定式によって検索条件を変更する
「こういう状態の場合は検索条件にこれを追加する」
Laravel のクエリビルダでは、こういったパターンでの SQL 文組み立てにも対応しています。
when メソッドを使い、第一引数に判定式の結果（boolean）、第二引数にクロージャを指定し、true であった場合の処理をクロージャに定義します。
$isMiddleAged = true;
$userBuilder->when($isMiddleAged, function (Builder $query): void {
$query->whereBetween('age', [40, 64]);
})
// true => select * from `users` where `age` between ? and ?
// false => select * from `users`
上記例の場合、
「isMiddleAged が true の場合は age が 40 〜 64 のユーザーに絞り込む」
という条件が追加されます。
もし false だった場合はクロージャをスルーするので、全件取得するという挙動になります。
ちなみに、false だった場合の条件も記述する事ができます。 その場合は以下のようにして、第三引数にもクロージャを定義し、そこへ false の場合の処理を記述します。
$isMiddleAged = true;
$userBuilder->when($isMiddleAged, function (Builder $query) {
$query->whereBetween('age', [40, 64]);
}, function (Builder $query) {
$query->where('age', '>', 20);
})
// true => select * from `users` where `age` between ? and ?
// false => select * from `users` where `age` > 20
これで、false の場合は全件取得ではなく 20 歳以上のユーザーのみ取得するという挙動になりました。
INSERT
テーブルへデータを登録（レコードを挿入）するには、insert メソッドを使います。
$userBuilder->insert([
'name' => 'Nadia Rippin',
'age' => 41,
'email' => 'brant.williamson@example.com',
]);
一度の SQL 文で複数のレコード挿入も可能です。
$userBuilder->insert([
[
'name' => 'test11',
'email' => 'test11@example.com',
],
[
'name' => 'test12',
'email' => 'test12@example.com',
]
]);
insertUsing
insertUsing メソッドは、サブクエリを使用して、テーブルに新しいレコードを挿入します。
下記例では、users テーブルの updated_at が 1 年前より古い レコードを抽出し、pruned_users テーブルに insert します。
DB::table('pruned_users')->insertUsing([
'id', 'name', 'email'
], $userBuilder->select(
'id', 'name', 'email'
)->where('updated_at', '<=', now()->subYear()));
upsert
upsert メソッドは、新しいレコードを挿入するか、既存のレコードを更新します。
下記例では、同じ email を持つレコードがあった場合は age を update し、レコードがなければ email と age でレコードを insert します。
$userBuilder->upsert(
[
[
'email' => 'edythe.volkman@example.net',
'age' => 24,
],
[
'email' => 'tquitzon@example.net',
'age' => 31,
]
],
['email'],
['age']
);
第一引数に insert or update するパラメータ、第二引数にどのカラムでレコード有無を判定するか、そして第三引数にレコードがあった場合に更新するカラムをしていします。
挿入後に自動増分 ID を取得する
テーブルが自動増分 ID（オートインクリメント＝ AUTO_INCREMENT）を持っている場合には、insertGetId メソッドでレコード挿入を行う事で、返り値に自動増分 ID を取得できます。
$userId = $userBuilder->insertGetId(
[
'name' => 'test11',
'email' => 'test11@test.com',
'password' => '123456',
'role' => 5
]
);
// $userId -> 123
自動増分 ID カラム名が id ではなく他の名前の場合は、第二引数にそのカラム名を指定する必要があります。
UPDATE
レコードを更新する場合には update メソッドを使います。
$userBuilder->where('id', '=', 1)
->update([
'name' => 'updated name',
'age' => 28,
]);
where を用いて更新するレコードを指定し、update メソッドにて変更するカラムとその値を配列に指定します。
updateOrInsert
updateOrInsert メソッドは、属性に一致するレコードを update し、無ければ insert します。
下記例では、 name と email が一致するレコードがあった場合には age を update し、無かった場合には name, email, age で insert します。
$userBuilder->updateOrInsert(
[
'name' => 'Ernestina Macejkovic',
'email' => 'edythe.volkman@example.net',
],
[
'age' => 29,
]
);
JSON カラムの更新
JSON カラムも更新が可能です。その場合はアロー演算子（->）で適切な変更対象を指定します。
$userBuilder->where('id', 1)
->update(['options->enabled' => true]);
カラムの値を増減させる単純な更新
例えば、カラムの中に何かのカウント数を持っていたとして、何かの時にその値を 1 だけ増やしたいとか減らしたいとか、もしくは任意の数だけ増減させたいなんて事もよくあります。
その時には increment() と decrement メソッドを使うと簡単に実装する事ができます。
$userBuilder->where('id', 1)->increment('age');
// age が 1 増加する
$userBuilder->where('id', 1)->increment('age', 3);
// age が 3 増加する
上記は increment() メソッドで age カラム（int）の値を加算させています。
第二引数に値を指定すればその分の数が、指定しなければ 1 が加算され更新されます。
$userBuilder->where('id', 1)->decrement('age');
// age が 1 減少する
$userBuilder->where('id', 1)->decrement('age', 3);
// age が 3 減少する
上記は decrement メソッドで age カラムの値を減算させています。
こちらも同様に、第二引数に値を指定すればその分の数が、指定しなければ 1 が減算され更新されます。
ちなみに上記は 1 件のレコードに対して更新を行っていますが、where を変更して対象のレコードに対してまとめて更新をかける事も可能です。
なお、増減させるだけではなく、他の通常の更新情報を配列で第三引数に渡す事で、増減以外の更新も行えます。
$userBuilder->where('id', 1)->increment('age', 3, ['name' => 'test1-2']);
上記のようにする事で、増減をおこないつつ、他のカラムに対しても通常の更新を行う事もできます。
DELETE
テーブルからレコードを削除するには delete メソッドを使います。
$userBuilder->where('id', '=', 1)
->delete();
上記では 1 件のみを削除していますが、where を変更すればまとめての削除も可能です。
TRUNCATE
delete メソッドは単純にレコードを削除するだけなので、もし全レコードを削除しても、自動増分 ID はリセットされません。
もしテーブル内を全て削除し、オートインクリメントもリセットしたい場合は、truncate() メソッドを使います。
$userBuilder->truncate();
排他制御
Laravel のクエリビルダでは、SELECT 文で悲観的ロックをかける事ができます。
共有ロック
sharedLock メソッドを使う事で、トランザクションがコミットされるまで SELECT している行が更新されることを防ぐことができます。
$userBuilder->where('id', 1)->sharedLock()->get();
占有ロック
lockForUpdate メソッドを使う事で、レコードを更新したり SELECT するために他の共有ロックが行われるのを防ぐことができます。
$userBuilder->where('id', 1)->lockForUpdate()->get();
