準備
クエリービルダーで使った以下の3つのテーブルで各メソッドを確認する。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
mysql> SELECT * FROM customers; +----+-----------+ | id | name | +----+-----------+ | 1 | customer1 | | 2 | customer2 | | 3 | customer3 | +----+-----------+ 3 rows in set (0.00 sec) mysql> SELECT * FROM emails; +----+-------------+--------------------+ | id | customer_id | email | +----+-------------+--------------------+ | 1 | 1 | customer1@mail.com | | 2 | 2 | customer2@mail.com | +----+-------------+--------------------+ 2 rows in set (0.00 sec) mysql> SELECT * FROM orders; +----+-------------+---------------------+---------------+ | id | customer_id | ordered_at | item | +----+-------------+---------------------+---------------+ | 1 | 1 | 2020-06-05 00:00:00 | screw | | 2 | 2 | 2020-06-05 00:00:00 | rubber sheet | | 3 | 2 | 2020-06-06 00:00:00 | plastic plate | | 4 | 1 | 2020-06-07 00:00:00 | rubber sheet | | 5 | 3 | 2020-06-08 00:00:00 | wire | +----+-------------+---------------------+---------------+ 5 rows in set (0.00 sec) |
これをordersに対してcustomersとemailsを左結合で関連付けて並べると以下のとおり。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> SELECT -> o.id, o.ordered_at, o.item, c.id, c.name, e.address -> FROM orders AS o -> LEFT JOIN customers AS c ON o.customer_id = c.id -> LEFT JOIN emails AS e ON c.id = e.customer_id; +----+---------------------+---------------+------+-----------+--------------------+ | id | ordered_at | item | id | name | address | +----+---------------------+---------------+------+-----------+--------------------+ | 1 | 2020-06-05 00:00:00 | screw | 1 | customer1 | customer1@mail.com | | 2 | 2020-06-05 00:00:00 | rubber sheet | 2 | customer2 | customer2@mail.com | | 3 | 2020-06-06 00:00:00 | plastic plate | 2 | customer2 | customer2@mail.com | | 4 | 2020-06-07 00:00:00 | rubber sheet | 1 | customer1 | customer1@mail.com | | 5 | 2020-06-08 00:00:00 | wire | 3 | customer3 | NULL | +----+---------------------+---------------+------+-----------+--------------------+ 5 rows in set (0.00 sec) |
抽出
select
SQLのSELECT句に対応し、データとして取り出す属性(=カラム)を指定。モデルに対応したテーブル名のFROM句も生成される。
モデルクラス::select('属性1', '属性2', ...)
↓
"select `属性1`, `属性2`, ... from `テーブル名`"
以下はOrderモデルのidとordered_at属性を取り出す例。
|
1 2 |
>>> Order::select('id', 'ordered_at')->toSql() => "select `id`, `ordered_at` from `orders`" |
全属性を取り出す場合は'*'を指定する。これはall()と同じ効果。
|
1 2 |
>>> Order::select('*')->toSql() => "select * from `orders`" |
where
モデルの属性に対して条件を課す。オペレーターは=、<などの算術演算子やIN、BETWEENなど。
モデルクラス::where('属性名', 'オペレーター', 値)
↓
"select * from `テーブル名` where `属性名` オペレーター ?"
- 生成されたSQLには値は直接埋め込まれず、プレースホルダー
?が置かれる - 値は別途バインドされる
以下はOrderモデルのデータうち、item属性が'rubber sheet'のものを抽出するためのSQLとバインディングされる値を確認したもの。
|
1 2 3 4 5 6 |
>>> Order::where('item', '=', 'rubber sheet')->toSql() => "select * from `orders` where `item` = ?" >>> Order::where('item', '=', 'rubber sheet')->getBindings() => [ "rubber sheet", ] |
WHERE … AND …
複数条件のANDは、それぞれの条件のwhere()メソッドをチェーンにする。
where(条件1)->where(条件2)->where...
↓
where 条件1 and 条件2 and ..."
以下はOrderモデルのデータのうち、item属性が'rubber sheet'で、かつid=2のデータを抽出するためのビルダー、SQLとバインド値。
|
1 2 3 4 5 6 7 |
>>> Order::where('item', '=', 'rubber sheet')->where('id', '=', 2)->toSql() => "select * from `orders` where `item` = ? and `id` = ?" >>> Order::where('item', '=', 'rubber sheet')->where('id', '=', 2)->getBindings() => [ "rubber sheet", 2, ] |
WHERE … OR …
複数条件のORは、orWhere()メソッドをチェーンにする。一番目はwhere()でもorWhere()でも結果は同じ
where(条件1)->orWhere(条件2)->orWhere...
↓
where 条件1 or 条件2 or ..."
以下はOrderモデルのデータのうち、itemが'screw'か'plastic plate'、またはid=3のデータを抽出するためのビルダーとSQL、バインド値。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
>>> Order::where('item', '=', 'screw')-> ... orWhere('item', '=', 'plastic plate')-> ... orWhere('customer_id', '=', 3)->toSql(); => "select * from `orders` where `item` = ? or `item` = ? or `customer_id` = ?" >>> Order::where('item', '=', 'screw')-> ... orWhere('item', '=', 'plastic plate')-> ... orWhere('customer_id', '=', 3)->getBindings(); => [ "screw", "plastic plate", 3, ] |
orWhere()
上記WHERE ... OR ...を参照。
where()とorWhere()の優先順位
3つ以上の条件を組み合わせる場合のANDとORの優先順位についてこちらでまとめている。
並べ替え
orderBy()
第1引数で並べ替えキーのカラムを、第2引数で'asc'／'desc'を指定。
orderBy('カラム名', 'asc'または'desc')
oldest()
引数で指定したカラムの昇順に並べ替える。引数を省略した場合はcreated_atフィールドの昇順、すなわち古い順に並べ替える。
latest()
引数で指定したカラムの降順に並べ替える。引数を省略した場合はcreated_atフィールドの降順、すなわち新しい順に並べ替える。
inRandomOrder()
データの順番をランダムに並べ替える。引数に整数のseedを与えることができる。
集約
groupBy()
クエリービルダーのcount()メソッドは整数値を返すため、Model::count(...)->groupBy(...)のような使い方ができない。
そこで、select()メソッドの引数にDB:raw()メソッドを使ってSQLのCOUNT句を書く。
以下の例では、Orderのitemでグループ化し、item名とそのかずをコレクションで取得してビューに渡している。
|
1 2 3 4 |
$counts = Order::select( 'item', DB::raw('count(item) as item_count')) ->groupBy('item')->get(); |
ビュー側でこれを表示する。
|
1 2 3 4 5 |
<ul> @foreach ($counts as $count) <li>{{ $count->item }}: {{ $count->item_count }}</li> @endforeach </ul> |
表示結果は以下のとおり。
- screw: 1
- rubber sheet: 2
- plastic plate: 1
- wire: 1
なお、クエリービルダーから生成されるSQLは以下のとおり。
|
1 2 3 |
>>> Order::select('item', DB::raw('count(item) as item_count'))-> ... groupBy('item')->toSql() => "select `item`, count(item) as item_count from `orders` group by `item`" |
having()
上の結果をhaving()で絞り込む例。使い方はwhere()と同じ。
|
1 2 3 4 5 6 |
$counts = Order::select( 'item', DB::raw('count(item) as item_count')) ->groupBy('item') ->having('item_count', '=', 1) ->get(); |
表示結果は以下のとおりで、カウント数が1の結果のみ抽出されている。
- screw: 1
- plastic plate: 1
- wire: 1
数の限定～データの一部
limit()
先頭からの個数を引数に指定してデータを限定。
offset()
limit()と組み合わせて使い、先頭から何個目のデータから取り出すかを指定。offset(3)->limit(2)の場合、3個目と4個目のデータが得られる。
結合
join()
キーを指定して、親モデルに子モデルを結合する。
join('子モデル', ’キー1', 'オペレーター', 'キー2')
以下はOrder～Customer～Emailを関連付ける例。
|
1 2 3 4 |
$orders = Order::select('*') ->join('customers', 'orders.customer_id', '=', 'customers.id') ->join('emails', 'customers.id', '=', 'emails.customer_id') ->get(); |
この結果、子モデルのCustomerやEmailの属性も直接Orderからアクセスできるようになる。
|
1 2 3 4 5 6 7 8 9 10 |
<ul> @foreach ($orders as $order) <li>{{ $order->id }}: {{ $order->ordered_at }}</li> <ul> <li>customer name: {{ $order->name }}</li> <li>email address: {{ $order->address }}</li> <li>ordered item : {{ $order->item }}</li> </ul> @endforeach </ul> |
joinはINNER JOINのため、emailが設定されていない(nullの)customer3は含まれない。
- 1: 2020-06-05 00:00:00
- customer name: customer1
- email address: customer1@mail.com
- ordered item : screw
- 2: 2020-06-05 00:00:00
- customer name: customer2
- email address: customer2@mail.com
- ordered item : rubber sheet
- 2: 2020-06-06 00:00:00
- customer name: customer2
- email address: customer2@mail.com
- ordered item : plastic plate
- 1: 2020-06-07 00:00:00
- customer name: customer1
- email address: customer1@mail.com
- ordered item : rubber sheet
leftJoin()
使い方はjoin()と同じで、LEFT JOIN句を生成する。上記のクエリービルダーでjoin()をleftJoin()に置き換えると、emailが登録されていないcustomer3も含まれる。
値の取出し
get()
クエリービルダーの実行結果を返す。戻り値はモデルインスタンスのコレクション。
find()
- find(n)
- 主キーの値を指定してデータを取り出す。戻り値はモデルインスタンス。
- find([m, n, …])
- 複数の主キーの値を指定してデータを取り出す。戻り値はモデルインスタンスのコレクション。
first()
クエリービルダーの実行結果の先頭データを返す。戻り値はモデルインスタンス。
pluck()
クエリービルダーの実行結果から、引数で指定したカラムのみ抜き出して返す。戻り値はカラムの内容の配列。
集計関数
全般論
集計関数の結果は数値
クエリービルダーにはSQLと同様の集計関数が準備されている。
ただしこれらは、ビルダーの結果に対して属性(カラム)を指定して集計した結果を数値で返すのみで、そのままselect()の引数として与えることはできない(SQLのようにselect()中で集計関数を使うときは、DB::raw()メソッドで生のSQLを書く必要がある)。
たとえば以下の例では全データの個数が整数で得られる。
|
1 |
Order::count('item'); |
where()で絞り込まれた結果のデータ数も得られる。以下はOrderのデータのうちcustomer_id=2のデータの個数を得る。
|
1 |
Order::where('customer_id', '=', 2)->count('id'); |
groupBy()による集約結果の集計はひと手間必要
groupBy()で絞り込んだ結果については、直接集計関数に渡しても適切な結果とならない(たとえばcount()で集計すると常に結果が1になる)。ビルダーの内容を見ると、where()の場合とgroupBy()の場合で構成が異なっている。
where()の結果のビルダーオブジェクト
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
Illuminate\Database\Eloquent\Builder {#1281 ▼ #query: Illuminate\Database\Query\Builder {#1256 ▼ +connection: Illuminate\Database\MySqlConnection {#1277 ▶} +grammar: Illuminate\Database\Query\Grammars\MySqlGrammar {#1279 ▶} +processor: Illuminate\Database\Query\Processors\MySqlProcessor {#1280} +bindings: array:9 [▶] +aggregate: null +columns: null +distinct: false +from: "orders" +joins: null +wheres: array:1 [▼ 0 => array:5 [▼ "type" => "Basic" "column" => "customer_id" "operator" => "=" "value" => 2 "boolean" => "and" ] ] +groups: null +havings: null |
groupBy()の結果のビルダーオブジェクト
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
Illuminate\Database\Eloquent\Builder {#1281 ▼ #query: Illuminate\Database\Query\Builder {#1256 ▼ +connection: Illuminate\Database\MySqlConnection {#1277 ▶} +grammar: Illuminate\Database\Query\Grammars\MySqlGrammar {#1279 ▶} +processor: Illuminate\Database\Query\Processors\MySqlProcessor {#1280} +bindings: array:9 [▶] +aggregate: null +columns: array:1 [▼ 0 => "id" ] +distinct: false +from: "orders" +joins: null +wheres: [] +groups: array:1 [▼ 0 => "id" ] +havings: null |
グループ化した結果をカウントしたい場合は、where()の引数にクロージャ―を入れて、その中にgropupBy()を含むクエリービルダーを書くとよい。
|
1 2 3 |
Order::where(function($q) { $q->select('id')->groupBy('id'); })->count('id'); |
DB::rawの方が明快
JOINで結合したテーブルの集計などが必要な場合、select()メソッド中にDB::ra()でSQLのcount()などを書く方法もある。
この場合はストレートにwhere()、join()、groupBy()などを連ねていけばいいので明快。たとえば以下のように書ける。
|
1 2 3 4 5 6 |
$members_per_group = \App\Member::select( 'groups.id as group_id', DB::raw('count(members) as member_count') ->join('groups', 'members.id', '=', 'groups.member_id') ->groupBy('groups.id') ->get(); |
なおコントローラー内でDB::raw()を使う場合は、冒頭で以下のuse宣言が必要。
|
1 |
use Illuminate\Support\Facades\DB; |
count()
引数で指定した属性でデータ数をカウントして返す。
sum()
引数で指定した属性の値の合計値を返す。
avg()
引数で指定した属性の値の合計を個数で割った平均値を返す。
min()
引数で指定した属性の値の最小値を返す。
max()
引数で指定した値の最大値を返す。