Laravel クエリビルダ記法まとめ
- 公開:
- 更新:
- カテゴリ: PHP Laravel
- タグ: PHP,Laravel,Database,QueryBuilder
クエリビルダとは、データベースからレコードを取得する際に SQL 文を組み立てて問い合わせを行いますが、それを簡単に組み立てる事の出来る機能です。
SQL 文に詳しくなくても、この機能によって適切な書式で問い合わせを行える為、とても便利な機能です。また、昨今の代表的なPHPフレームワークではおおよそ提供されている機能でもあります。
今回は Laravel の QueryBuilder について、その機能を入門レベルでもわかるように少しだけ噛み砕いて記載してあります。
- アジェンダ
-
- 検証環境
- クエリビルダを使用する為の基本書式
- 結果データ取得
- 分割処理
- 集計
- SELECT
- DISTINCT
- SQL文を直接記述する
- JOIN
- UNION
- WHERE
- orderBy
- グループ化(GROUP BY)と絞り込み(HAVING)
- 取得レコード数の制限・スキップ
- 判定式によって検索条件を変更する
- INSERT
- UPDATE
- DELETE
- 悲観的ロック
検証環境
今回の検証環境は以下の通りです。
- Laravel 7.4
クエリビルダを使用する為の基本書式
基本的な書式を以下に示します。
use Illuminate\Support\Facades\DB; // DB ファサードを use する
public function getDate()
{
// テーブルを指定
$users = DB::table('users');
}
- DB ファサードを use します。
- DB ファサードの table メソッドを使い、引数に取得したいテーブル名を渡します。
今回は例として users テーブルから情報を取得する為のクエリビルダを記述していきますが、users テーブルのクエリビルダインスタンスを格納した変数 $users に対してクエリを記述し、結果を取得していきます。
これから先は全て、このベースとなる記述の上に、該当のメソッドを記述していく流れになります。
結果データ取得
条件を記述していく前に、結果データの取得を以下に示します。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`
分割処理
レコードを取得し処理を行おうとした際に、対象レコードが数万件レベルであった場合は一度に取得する事は難しいかもしれません。
そんな時は chunk() メソッドを使えば、取得条件に対して分割処理を行う事ができます。
$users->orderBy('id')->chunk(100, function ($users) {
foreach ($users as $user) {
//
}
});
上記の例では、usersテーブルに対し100件ずつレコードを取得し、クロージャの中でそれらを処理する事ができます。
例えば、users の全体件数は 10,000 件だけど、100 件ずつ取り出して処理を行ったり。リソースを節約しながら処理を行っていくことができます。
1点気を付ける事は、chunk() メソッドを使用する際は必ず orderBy() でデータの並び順を指定する必要があります。
ちなみに、クロージャ内で false を返すと分割処理を中断することができます。
$users->orderBy('id')->chunk(100, function ($users) {
foreach ($users as $user) {
// 処理
if($expr) {
// チャンク中段
return false;
}
}
});
集計
Laravelのクエリビルダでは、一通りの集計メソッドが準備されています。
条件を指定した後に、結果データ取得の時と同様に最後に以下のメソッドをチェインする(つなげる)事で、結果が得られます。
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
引数にはカラム名を指定します。
SELECT
取得するカラムを指定する為には、select() メソッドを利用します。
$data = $users->select('name', 'email as user_email')->get();
引数に取得したいカラムを渡しますが、上記の通り、エイリアスも書けます。
// 取得結果
[0] => stdClass Object
(
[name] => user01
[user_email] => test01@test.com
)
[1] => stdClass Object
(
[name] => user02
[user_email] => test02@test.com
)
select()メソッドにて取得メソッドを定義した後で、さらに追加で取得カラムを追加する場合は、addSelect()メソッドで追加が行えます。
$users->select('name', 'email as user_email');
// 取得カラムを追加する
$data = $users->addSelect('id')->get();
// 取得結果
[0] => stdClass Object
(
[name] => user01
[user_email] => test01@test.com
[id] => 1
)
[1] => stdClass Object
(
[name] => user02
[user_email] => test02@test.com
[id] => 2
)
DISTINCT
distinct()メソッドを用いると、重複行をまとめた結果が取得できます。
$data = $users->distinct()->get();
SQL文を直接記述する
クエリビルダの中で、直接SQL文を記述する事もできます。
DB::rawメソッドを使ってSQL文を記述していきます。
$data = $users->select(DB::raw('COUNT(*) AS user_count'))->get();
rawメソッド
DB::rawは最も基本的なSQL文挿入メソッドです。更に各セクション毎に用意されているrawメソッドを使う事によって、記述を短縮できます。
selectRaw
selectRawメソッドは select(DB::raw(...)) 式を置き換えます。
$data = $users->selectRaw('COUNT(*) AS user_count')->get();
whereRaw / orWhereRaw
whereRawとorWhereRawメソッドは、WHERE節へSQL文を直接挿入できます。
$data = $users->whereRaw('`role` = 1')->get();
また、第二引数に配列の形で値を渡してあげる事で、パラメーターをバインドできます。
$data = $users->whereRaw('`role` = ? AND id < ?', [1, 5])->get();
groupByRaw
groupByRaw メソッドは、GROUP BY 節 の値を指定できます。
// 作成日時ごとのユーザー数を求める
$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
// )
// .
// .
// .
// )
//)
havingRaw / orHavingRaw
havingRaw と orHavingRaw メソッドは、GROUP BY を行った際にhaving節へSQL文を直接挿入できます。
$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
)
orderByRaw
orderByRaw メソッドは、ORDER BY節へSQL文を直接挿入できます。
$data = $users
->orderByRaw('updated_at - created_at DESC')
->get();
JOIN
クエリビルダでJOINを書く場合は、以下のようになります。
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させる為の条件式を記述します。
UNION
UNION(クエリ結合)を行う場合は、union() / unionAll() メソッドを使います。
$data1 = DB::table('users')
->where('id', '<', 5);
$data2 = $users
->where('id', '>=', 5)
->union($data1)
->get();
上記のように、クエリ結合を行うインスタンスをunion()メソッドで渡してあげます。
ちなみに上記では、別にもう一つクエリビルダインスタンスを生成していますが、同じインスタンスでUNIONを行うと、最大関数ネストレベルの設定値によってはエラーが出ますので注意です。
WHERE
ここからは条件付与(WHERE句)について記載していきます。
ベーシックな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句に関するメソッドは以下の通りです。
whereIn / orWhereIn
whereIn()メソッドを使うと、IN節(含まれる)を記述できます。
$data = $users->whereIn('id', [2, 5, 9])->get();
whereNotIn / orWhereNotIn
whereNotIn()メソッドの場合はNOT IN節(含まれない)を記述できます。
$data = $users->whereNotIn('id', [2, 5, 9])->get();
「含まれる・含まれない」は完全一致である点に注意してください。”あいまい”の意ではありません。
Null判別
null判定のメソッドは以下の通りです。
whereNull / orWhereNull
whereNull()メソッドを使うと、カラムの値がnullである条件を記述できます。
$data = $users->whereNull('email')->get();
whereNotNull / orWhereNotNull
対して、whereNotNull()メソッドを使うと、カラムの値がnullではない条件を記述できます。
$data = $users->whereNotNull('email')->get();
日時比較
日時の比較を行う際にも、Laravelのクエリビルダには専用のメソッドが用意されています。
whereDate / orWhereDate
日付比較にはwhereDate()メソッドを使います。
$data = $users->whereDate('updated_at', '2018-03-21')->get();
whereMonth / orWhereMonth
月の比較には、whereMonth()メソッドを使います。
$data = $users->whereMonth('updated_at', 2)->get();
whereDay / orWhereDay
日付の値そのものとの比較にはwhereDay()メソッドを使います。
$data = $users->whereDay('updated_at', 25)->get();
whereYear / orWhereYear
年の比較には、whereYear()メソッドを使います。
$data = $users->whereYear('updated_at', 2018)->get();
whereTime / orWhereTime
時間の比較には、whereYear()メソッドを使います。
$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();
whereJsonContains / orWhereJsonContains
whereJsonContains()メソッドを使う事もできます。通常の使い方もできる他、条件値に複数の値を指定する事もできます。
$data = $users
->whereJsonContains('options->language', 'ja')
->get();
// 複数の値を指定する
$data = $users
->whereJsonContains('options->language', ['ja', 'en'])
->get();
whereJsonLength / orWhereJsonLength
whereJsonLength()メソッドを使う事でデータ長を指定できます。
$data = $users
->whereJsonLength('team->user', 5)
->get();
$data = $users
->whereJsonLength('team->user', '>', 5)
->get();
データ長とは、つまり個数です。上記メソッド例では、「userを5つ(より多い)持ったデータ」という意味になります。
[{
"team": {
"user": [
{"name": "user01"},
{"name": "user02"},
{"name": "user03"},
{"name": "user04"},
{"name": "user05"},
]
}
},{
"team": {
"user": [
{"name": "user001"},
{"name": "user002"},
{"name": "user003"},
]
}
}]
orderBy
orderBy()メソッドを使うと、指定したカラムで結果データをソートします。
$data = $users->orderBy('id', 'desc')->get();
第一引数にカラム名、第二引数に昇順(asc)、もしくは降順(desc)のどちらかを指定します。
単純ソート
単純なソートに関しては、専用のソートメソッドが用意されています。
降順
latest() メソッドを使うと、降順で取得します。
$data = $users->latest()->get();
昇順
oldest() メソッドを使うと、昇順で取得します。
$data = $users->oldest()->get();
上記二つのメソッドに関しては、デフォルトとして作成日(created_atカラム)をキーとして、ソートを行いますが、引数にカラム名を渡す事によって指定したカラムでソートが行えます。
ランダムで並び替え
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();
グループ化(GROUP BY)と絞り込み(HAVING)
集計を行う際に GROUP BY や HAVING を使いたい場面もよくあると思いますが、その場合はgroupBy()メソッド、そしてhaving()メソッドを使います。
$data = $users
->selectRaw('role, sum(role) AS role_cnt')
->groupBy('role')
->having('role', '>', 5)
->get();
// => SELECT role, sum(role) AS role_cnt FROM `users` GROUP BY `role` HAVING `role` > 5
もちろん、groupBy()メソッドに複数のカラムを指定する事も可能です。
$data = $users
->selectRaw('`role`, sum(role) AS role_cnt')
->groupBy('role', 'gender')
->having('role', '>', 5)
->get();
// => SELECT `role`, sum(role) AS role_cnt FROM `users` GROUP BY `role`, `gender` HAVING `role` > 5
取得レコード数の制限・スキップ
条件に対して返ってくる結果レコードの数を制限したり、件数をスキップ(飛ばす)したりもできます。
LIMIT
取得レコード数を制限するには、limit()メソッドを使います。
$data = $users->limit(3)->get();
OFFSET
結果レコードの取得開始位置(何件目から取得するか)を指定するには、offset()メソッドを使います。
$data = $users
->offset(2)
->limit(3)
->get();
OFFSETは得てしてLIMIT節と組み合わせて使う事が多いですが、上記の例の場合では「2件目から3件取得する」という意味になります。
尚、limit()とoffset()のシノニム(同じもの)として、take() と skip() というメソッドもあります。
$data = $users
->skip(2)
->take(3)
->get();
skip() は offset() と、take() は limit() と同じです。
MySQLなどを知る人にはLIMITやOFFSETはお馴染みですが、そうでもない人にはskip() や take() の方がわかりやすいかもしれません。どちらを使っても同じ結果になります。
判定式によって検索条件を変更する
「リクエストであのパラメータが来た場合は、検索条件にこれを追加する」
のような流れは良くあるパターンです。
Laravelのクエリビルダには、そのパターンでのSQL文組み立てにも対応しています。
when()メソッドを使い、第一引数に判定式の結果(trueもしくはfale)、第二引数にクロージャを指定し、trueであった場合の処理をクロージャに定義します。
$user_id = 8;
$data = $users
->when($user_id, function ($query) use ($user_id) {
return $query->where('id', $user_id);
})
->get();
上記例の場合、
「$user_idがtrue(すなわち、値が入っている)の場合は、idカラムをその値で指定する」
という条件が追加されます。
もしfalseだった場合はクロージャをスルーするので、全件取得するという挙動になります。
ちなみに、falseだった場合の条件も記述する事ができます。 その場合は以下のようにして、第三引数にもクロージャを定義し、そこへfalseの場合の処理を記述します。
$user_id = null;
$data = $users
->when($user_id, function ($query) use ($user_id) {
return $query->where('id', $user_id);
}, function ($query) {
return $query->limit(3);
})
->get();
これで、falseの場合は全件取得ではなく3件のみ取得するという挙動になりました。
INSERT
テーブルへデータを登録(レコードを挿入)するには、insert()メソッドを使います。
$data = $users
->insert(
[
'name' => 'test11',
'email' => 'test11@test.com',
'password' => '123456',
'role' => 5
]
);
1つの配列に1件のデータを格納(プロパティ名をカラム名にする)しています。
これを、一つの配列内で複数格納(連想配列の形に)する事によって、一度のSQL文で複数のレコードを挿入できます。
$data = $users
->insert([
[
'name' => 'test11',
'email' => 'test11@test.com',
'password' => '123456',
'role' => 5
],
[
'name' => 'test12',
'email' => 'test12@test.com',
'password' => '7891011',
'role' => 5
]
]);
挿入後に自動増分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ではなく他の名前の場合は、第二引数にそのカラム名を指定する必要があります。
UPDATE
レコードを更新する場合にはupdate()メソッドを使います。
$data = $users
->where('id', 1)
->update([
'name' => 'test1-2',
]);
where節を用いて更新するレコードを指定し、update()メソッドにて変更対象のレコード(カラム・値)を配列に指定します。
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']);
上記のようにする事で、増減をおこないつつ、他のカラムに対しても通常の更新を行う事ができます。
DELETE
テーブルからレコードを削除するにはdelete()メソッドを使います。
$users->where('id', 1)->delete();
上記では1件のみを削除していますが、where節を変更すればまとめての削除も可能です。
TRUNCATE
delete()メソッドは単純にレコードを削除するだけなので、もし全レコードを削除しても、自動増分IDはリセットされません。
もしテーブル内を全て削除し、オートインクリメントもリセットしたい場合は、truncate()メソッドを使います。
$users->truncate();
悲観的ロック
Laravelのクエリビルダでは、SELECT文で悲観的ロックをかける事ができます。
共有ロック
sharedLock()メソッドを使う事で、トランザクションがコミットされるまでSELECTしている行が更新されることを防ぐことができます。
$data = $users->where('id', 1)->sharedLock()->get();
占有ロック
lockForUpdate()メソッドを使う事で、レコードを更新したりSELECTするために他の共有ロックが行われるのを防ぐことができます。
$data = $users->where('id', 1)->lockForUpdate()->get();
まとめ
このように、Laravelのクエリビルダは豊富な機能を備えており、大概のSQL文は組み立てる事ができます。
また、SQL文の文法そのものを知る事でも、こういった機能もさらに理解が深まると思います。
現在でも機能追加は行われており、今後もっともっと便利になると思うので、是非試してみてください。