BookmarkLINEクエリビルダとは、データベースからレコードを取得する際にSQL文を組み立てて問い合わせを行いますが、それを簡単に組み立てる事の出来る機能です。SQL文に詳しくなくても、この機能によって適切な書式で問い合わせを行える為、とても便利な機能です。昨今の代表的なPHPフレームワークではおおよそ提供されている機能でもあります。今回はLaravelのQueryBuilderについて、その機能を入門レベルでもわかるように少しだけ噛み砕いて記載してあります。BY)と絞り込み(HAVING)取得レコード数の制限・スキップLIMITOFFSET判定式によって検索条件を変更するINSERT挿入後に自動増分IDを取得するUPDATEJSONカラムの更新カラムの値を増減させる単純な更新DELETETRUNCATE悲観的ロック共有ロック占有ロック検証環境Laravelのバージョンに関しては、5.7/5.6/5.5/5.4/5.3にて動作確認済みです。5.6.0リリース以降で新機能として追加されたものについては注釈をつけているので、自身のバージョンと照らし合わせて使ってください。今回はコントローラの中でクエリビルダを使っていきますが、基本的な書式を以下に示します。}}まず、DBファサードをuseします。DBファサードのtableメソッドを使い、引数に取得したいテーブル名を渡す(上記では「users」)事でそのテーブルのクエリビルダインスタンスが取得します。今回は例としてユーザ情報テーブルから情報を取得する為のクエリビルダを記述していきますが、usersテーブルのクエリビルダインスタンスを格納した変数$usersに対してチェインでクエリを記述し、結果を取得していく事になります。これから先は全て、このベースとなる記述の上に、該当のメソッドを記述していく流れになります。条件を記述していく前に、結果データの取得を以下に示します。Laravelのクエリビルダには以下の取得メソッドがあります。get()条件指定に沿った結果データの全てを取得します。$users->get();取得出来る結果データは、StdClassオブジェクトのインスタンスを結果として含むCollectionオブジェクトとして返ってきます。個々の結果データにアクセスするには、以下のようにループで回したり、オブジェクトから直接取得する事も出来ます。$data[0]->name;first()結果データの最初の1件のみを取得します。結果が何件であっても、1件のみを取得します。$users->first();結果データはStdClassオブジェクトで取得されます。取り出すにはオブジェクトのプロパティにアクセスします。$data->name;value()value()メソッドで指定した1つのカラムのみを取得します。$users->value('email');ただしこのメソッドは条件に限らず1件のみを取得するので、where句などと併用して狙った1件を取得する際などに使います。test01@test.compluck()pluck()メソッドで指定カラム1つだけをコレクションで取得出来ます。$users->pluck('email');こちらはvalue()メソッドとは違い、条件の結果全てのデータを取得します。test10@test.com)データへのアクセスは、ループでも直接でも取り出しが可能です。$data[0];ちなみに、取得コレクションの識別子(プロパティ)カラムを指定する事も可能です。'name');第一引数に取り出すカラムを、第二引数にプロパティカラムを指定する事で。key-valueの形での取得が行えます。test10@test.com)exists()exists()メソッドを使うと、条件に対してレコードが存在するかを確認できます。レコードは存在しない}doesntExist()doesntExist()メソッドはexists()メソッドの逆です。レコードは存在する}toSql()toSql()メソッドで、発行しようとしているSQL文を確認できます。$users->toSql();結果を格納した変数$dataにSQL文が格納されます。`users`分割処理レコードを取得し処理を行おうとした際に、対象レコードが数万件レベルであった場合は一度に取得する事は難しいかもしれません。そんな時はchunk()メソッドを使えば、取得条件に対して分割処理を行う事が出来ます。}});上記の例では、usersテーブルに対し100件ずつレコードを取得し、クロージャの中でそれらを処理する事が出来ます。1点気を付ける事は、chunk()メソッドを使用する際は必ずorderBy()メソッドでデータの並び順を指定する必要があります。ちなみに、クロージャ内でfalseを返すと分割処理を中断することができます。}});集計Laravelのクエリビルダでは、一通りの集計メソッドが準備されています。条件を指定した後に、結果データ取得の時と同様に最後に以下のメソッドをチェインする(つなげる)事で、結果が得られます。count()count()メソッドで、レコードの件数を取得できます。10(件)max()max()メソッドで、指定カラムの最大値を取得できます。10引数にはカラム名を指定します。min()min()メソッドで、指定カラムの最小値を取得できます。1引数にはカラム名を指定します。avg()avg()メソッドで、指定カラムの平均値を取得できます。5.5000引数にはカラム名を指定します。sum()sum()メソッドで、指定カラムの合計値を取得できます。55引数にはカラム名を指定します。SELECT取得するカラムを指定する為には、select()メソッドをチェインします。user_email')->get();引数に取得したいカラムを渡しますが、上記の通り、エイリアスも書けます。)select()メソッドにて取得メソッドを定義した後で、さらに追加で取得カラムを追加する場合は、addSelect()メソッドで追加が行えます。2)DISTINCTdistinct()メソッドを用いると、重複行をまとめた結果が取得できます。$users->distinct()->get();SQL文を直接記述するクエリビルダの中で、直接SQL文を記述する事も出来ます。DB::rawメソッドを使ってSQL文を記述していきます。user_count'))->get();rawメソッドDB::rawは最も基本的なSQL文挿入メソッドです。各セクション毎に用意されているrawメソッドを使う事によって、記述を短縮できます。orWhereRawwhereRawとorWhereRawメソッドは、WHERE節へSQL文を直接挿入できます。1')->get();また、第二引数に配列の形で値を渡してあげる事で、パラメーターをバインドできます。行った際にhaving節へSQL文を直接挿入できます。BY節へSQL文を直接挿入できます。->get();JOINクエリビルダでJOINを書く場合は、以下のようになります。JOINjoin()メソッドを使います。->get();上記のようにjoin()メソッドをチェインする事で、複数のテーブルを結合できます。JOINleftJoin()メソッドを使います。JOINcrossJoin()メソッドを使います。クロス結合を行いたいテーブルを指定する事で、基テーブルと指定テーブルのデカルト積(すべての行の組み合わせ)を取得できます。$users->crossJoin('role')->get();複雑なJOINJOINの条件が通常よりも複雑な場合は、第二引数にクロージャを指定し定義する事で、実現できます。->get();クロージャの中でWHERE句を用いることもできます。)第一引数にサブクエリオブジェクト、第二引数にはそれらを示す任意の識別子、第三引数にクロージャでJOINさせる為の条件式を記述します。※これらのメソッドはver5.6.17以降で使用可能です。->get();上記のように、クエリ結合を行うインスタンスをunion()メソッドで渡してあげます。ちなみに上記では、別にもう一つクエリビルダインスタンスを生成していますが、同じインスタンスでUNIONを行うと、最大関数ネストレベルの設定値によってはエラーが出ますので注意です。WHEREここからは条件付与(WHERE句)について記載していきます。ベーシックなWHERE節ポピュラーなWHERE節を使うには、where()メソッドを使います。引数にカラム名、第二引数に比較演算子、またはSQLで使うオペレータ、第三引数に比較する値を指定します。5)->get();第二引数がイコール、つまり等しい場合に限っては、比較演算子を省略し、引数は2つで取得できます。5)->get();LIKE句も、同様に三つの引数で取得できます。WHERE句を形成できますが、配列で渡す事によって一度のwhere()メソッドでそれを実現できます。先に紹介したものはすべて、チェインでつなげるとANDでのWHERE句になりますが、当然、ORも記述できます。その場合は、orWhere()メソッドを使います。->get();whereBetweenwhereBetween()メソッドを使うと、BETWEEN節(AとBの間)を記述できます。6])->get();第一引数に対象カラム、第二引数には配列でそれぞれ、対象の値を渡します。BETWEEN節(AとBの間ではない)を記述できます。6])->get();第一引数に対象カラム、第二引数には配列でそれぞれ、対象の値を渡します。IN節IN句に関するメソッドは以下の通りです。whereInwhereIn()メソッドを使うと、IN節(含まれる)を記述できます。IN節(含まれない)を記述できます。9])->get();「含まれる・含まれない」は完全一致である点に注意してください。”あいまい”の意ではありません。Null判別null判定のメソッドは以下の通りです。whereNullwhereNull()メソッドを使うと、カラムの値がnullである条件を記述できます。$users->whereNull('email')->get();whereNotNull対して、whereNotNull()メソッドを使うと、カラムの値がnullではない条件を記述できます。日時の比較を行う際にも、Laravelのクエリビルダには専用のメソッドが用意されています。orWhereDate日付比較にはwhereDate()メソッドを使います。'2018-03-21')->get();※orWhereDate()はver5.6.10以降で使用可能です。(2018-04-30)で引数は2つだけという事でFixしたようです。orWhereMonth月の比較には、whereMonth()メソッドを使います。2)->get();※orWhereMonth()はver5.6.10以降で使用可能です。orWhereDay日付の値そのものとの比較にはwhereDay()メソッドを使います。25)->get();※orWhereDay()はver5.6.10以降で使用可能です。orWhereYear年の比較には、whereYear()メソッドを使います。2018)->get();※orWhereYear()はver5.6.10以降で使用可能です。whereTime時間の比較には、whereYear()メソッドを使います。'12:00')->get();この辺りの検索には自由度が結構ある(時間だけとか日付の値だけはあまり検索しないので複合的な使い方をする必要もある)ので使い道を考える必要がありますが、通常ではDATE_FORMAT()しなければいけないところをこうしてメソッド一つで実現できてしまうのはとても便利です。whereColumnwhereColumn()メソッドを使うと。二つのカラムが同じ値である事を確認できます。'updated_at')->get();このメソッドの素晴らしいところは、同値判定だけでなく、比較演算子も使える事です。'updated_at')->get();そしてそれらは、配列で渡す事で複数設定できます。xxx)のようなSQL文も組み立てたい状況があります。LaravelのクエリビルダはこういったSQL文の組み立てにももちろん対応しています。where()メソッド、もしくはorWhere()メソッドの中で、クロージャを定義します。相関副問合せ(副問合せ側から主問合せの列を参照する)にも対応しています。whereExists()メソッドの中でクロージャを定義し、その中で必要な条件を記述します。Server2016・SQLite3.9.0であれば、JSONカラムに対してのクエリにも対応しています。使用するメソッドは通常のwhere()やorWhere()などでOKですが、アロー演算子で指定しているところが特徴です。orWhereJsonContainswhereJsonContains()メソッドを使う事も出来ます。通常の使い方もできる他、条件値に複数の値を指定する事もできます。->get();※whereJsonContains()はver5.6.25以降で使用可能です。通常の使い方はSQLite以外で、複数値はMySQLとPostgreSQLのみで使用可能です。orWhereJsonLengthwhereJsonLength()メソッドを使う事でデータ長を指定出来ます。->get();データ長とは、つまり個数です。上記メソッド例では、「userを5つ(より多い)持ったデータ」という意味になります。}}]※whereJsonLength()はver5.7以降で使用可能です。orderByorderBy()メソッドを使うと、指定したカラムで結果データをソートします。'desc')->get();第一引数にカラム名、第二引数に昇順(asc)、もしくは降順(desc)のどちらかを指定します。単純ソート単純なソートに関しては、専用のソートメソッドも用意されています。降順latest()メソッドを使うと、降順で取得します。$users->latest()->get();昇順oldest()メソッドを使うと、昇順で取得します。$users->oldest()->get();上記二つのメソッドに関しては、デフォルトとして作成日(created_atカラム)をキーとして、ソートを行いますが、引数にカラム名を渡す事によって指定したカラムでソートが行えます。ランダムで並び替えinRandomOrder()メソッドを使うと、結果データをランダムな順番に出来ます。使いたい場面もよくあると思いますが、その場合はgroupBy()メソッド、そしてhaving()メソッドを使います。5もちろん、groupBy()メソッドに複数のカラムを指定する事も可能です。5取得レコード数の制限・スキップ条件に対して返ってくる結果レコードの数を制限したり、件数をスキップ(飛ばす)したりもできます。LIMIT取得レコード数を制限するには、limit()メソッドを使います。$users->limit(3)->get();OFFSET結果レコードの取得開始位置(何件目から取得するか)を指定するには、offset()メソッドを使います。->get();OFFSETは得てしてLIMIT節と組み合わせて使う事が多いですが、上記の例の場合では「2件目から3件取得する」という意味になります。いうメソッドもあります。方がわかりやすいかもしれません。どちらを使っても同じ結果になります。判定式によって検索条件を変更する「リクエストであのパラメータが来た場合は、検索条件にこれを追加する」のような流れは良くあるパターンです。Laravelのクエリビルダには、そのパターンでのSQL文組み立てにも対応しています。when()メソッドを使い、第一引数に判定式の結果(trueもしくはfale)、第二引数にクロージャを指定し、trueであった場合の処理をクロージャに定義します。「$user_idがtrue(すなわち、値が入っている)の場合は、idカラムをその値で指定する」という条件が追加されます。falseだった場合はクロージャをスルーするので、全件取得するという挙動になります。ちなみに、falseだった場合の条件も記述する事ができます。その場合は以下のようにして、第三引数にもクロージャを定義し、そこへfalseの場合の処理を記述します。->get();これで、falseの場合は全件取得ではなく3件のみ取得するという挙動になりました。INSERTテーブルへデータを登録(レコードを挿入)するには、insert()メソッドを使います。);1つの配列に1件のデータを格納(プロパティ名をカラム名にする)しています。これを、一つの配列内で複数格納(連想配列の形に)する事によって、一度のSQL文で複数のレコードを挿入できます。]);挿入後に自動増分IDを取得するテーブルが自動増分ID(オートインクリメント=AUTO_INCREMENT)を持っている場合には、insertGetId()メソッドでレコード挿入を行う事で、返り値に自動増分IDを取得できます。idでなければなりません。自動増分IDカラム名がidではなく他の名前の場合は、第二引数にそのカラム名を指定する必要があります。UPDATEレコードを更新する場合にはupdate()メソッドを使います。]);where節を用いて更新するレコードを指定し、update()メソッドにて変更対象のレコード(カラム・値)を配列に指定します。JSONカラムの更新もちろんJSONカラムも更新が可能です。その場合はアロー演算子(->)で適切な変更対象を指定します。カラムの中に何かのカウント数を持っていたとして、何かの時にその値を1だけ増やしたいとか減らしたいとか、もしくは任意の数だけ増減させたいなんて事もよくあります。その時にはincrement()とdecrement()メソッドを使うと簡単に実装する事が出来ます。3);上記はincrement()メソッドでcountカラムの値を加算させています。第二引数に値を指定すればその分の数が、指定しなければ1が加算され更新されます。3);上記はdecrement()メソッドでcountカラムの値を減算させています。こちらも同様に、第二引数に値を指定すればその分の数が、指定しなければ1が減算され更新されます。ちなみに上記は1件のレコードに対して更新を行っていますが、where節を変更して対象のレコードに対してまとめて更新をかける事ももちろん可能です。increment()とdecrement()メソッドの使えるところは、増減させるだけではなく、他の通常の更新情報を配列で第三引数に渡す事で、増減以外の更新も行える事です。'test1-2']);上記のようにする事で、増減をおこないつつ、他のカラムに対しても通常の更新を行う事が出来ます。DELETEテーブルからレコードを削除するにはdelete()メソッドを使います。1)->delete();上記では1件のみを削除していますが、where節を変更すればまとめての削除も可能です。TRUNCATEdelete()メソッドは単純にレコードを削除するだけなので、もし全レコードを削除しても、自動増分IDはリセットされません。テーブル内を全て削除し、オートインクリメントもリセットしたい場合は、truncate()メソッドを使います。$users->truncate();悲観的ロックLaravelのクエリビルダでは、SELECT文で悲観的ロックをかける事が出来ます。共有ロックsharedLock()メソッドを使う事で、トランザクションがコミットされるまでSELECTしている行が更新されることを防ぐことが出来ます。1)->sharedLock()->get();占有ロックlockForUpdate()メソッドを使う事で、レコードを更新したりSELECTするために他の共有ロックが行われるのを防ぐことが出来ます。1)->lockForUpdate()->get();まとめこのように、Laravelのクエリビルダは豊富な機能を備えており、大概のSQL文は組み立てる事が出来ます。(集計系のSELECTあたりが整備されたらほとんどSQL文の直書きは不要になるんじゃないかな)また、SQL文の文法そのものを知る事でも、こういった機能もさらに理解が深まると思います。現在でも機能追加は行われており、今後もっともっと便利になると思うので、是非試してみてください。SESを使ってAPIメール送信を行う(Mailableクラス&SimpleEmailService)2019.12.3MySQL8.0.16で追加されたCHECK制約を試してみる。RitoLaboこのサイトについて利用規約プライバシーポリシ。