laravel 5.2 Eager Loadingによる集約関数(MAX, MIN, COUNT, SUM…)のやり方

なんだか気温が安定しませんね。

春っぽくなってきました。

 

今回は laravel の eager loading についてです。

eager loading って名前聞いただけだと全然わからないですよね。。laravelってこういうの多い気がします。

 

eager loading とはなんぞや?ということは

http://readouble.com/laravel/5/1/ja/eloquent-relationships.html#eager-loading

を見てもらえば早いのですが、要するに「リレーション先のレコードを IN 句でまとめて取ってきて、一つの結果にまとめますよ」っていう機能です。

伝わりにくいですね。

正直普通の1対1のリレーションなら使う必要はないと思います。

 

単純な実例で示しましょう。

 

今回、こんな二つのテーブルを考えます。test_user テーブルと test_user_item テーブル です。

userテーブルitemテーブル

osanai, shinoda, takenaka の3人がいて、それぞれアイテムを持っています。

 

osanai は 「1億円のお金と2千万円の車」 を持っています。

shinoda は 何も持ってない。

takenaka は「1000円の鉛筆、100円のノート、200円の消しゴム」 を持っています。

格差を感じます。

 

ここで「各ユーザーが持っているアイテムの最高金額が知りたい」という場合です。

SQLで言えば、

SELECT test_user.user_id, test_user.user_name, MAX(test_user_item.price) as max_price

FROM test_user LEFT JOIN test_user_item ON test_user.user_id = test_user_item.user_item

GROUP BY test_user_item.user_id;

 

です。

もちろんこのまま書いてもいいんですが、ページネーションの時とかに少し面倒です。

もともとは GROUP BY 必要ないのに、1:多 の複数レコード対応のために、(test_user_itemテーブルに、1つのuser_idに対応する複数のレコードがあるから)

GROUP BYを追加しなくちゃならないのはなぁ。。。みたいな場合ですね。

 

特にページネーションでは1ページに表示する件数が100件以下の場合が多いので、取得した全レコードに対してGROUP BY をするのは非効率なときもあります。

なので、「今開いているページのuser_idに対応するアイテムのデータだけ取ってきたい」ってやれたらいいんじゃない?を実現したいわけです。

つまり、

 

1つめのクエリ

SELECT test_user.user_id, test_user.user_name FROM test_user LEFT JOIN test_user_item ON test_user.user_id = test_user_item.user_id ;

 

2つめのクエリ

SELECT MAX(test_user_item.price) as max_price FROM test_user_item WHERE test_user_item.user_id IN (…必要なuser_id(1つめのクエリで取得したuser_id))

GROUP BY test_user_item.user_id;

 

みたいにしたい。

こうすると必要なuser_idでだけデータを取得できてスピードも速いのでは?ってことですね。

これを実現するのがeager loadingです。

 

設定の順番は、

 

1. リレーションを設定する。

2. 1のリレーション設定の中で取得時に発行するSQLを書く

3. Model::withメソッドでeager loading設定

です。

 

1. リレーションの設定

まず、test_user テーブルに対応する TestUser クラスと、test_user_item テーブルに対応する TestUserItem クラスを作成します。

WS000252

WS000258

そして、メインとなるテーブル(今回はtest_user テーブル)に対応するTestUserクラスに、JOIN先のテーブル(test_user_itemテーブル)に対応する

クラス名のメソッド(public function TestUserItem)を作成します。

このメソッドの中で、リレーションを書いていきます。

 

2. リレーション時に発行するSQLを書く

今回は、最終的に「各user_id に対応するアイテムの最高価格が “1つ” 欲しい」ので、has one (1:1)です。

実は 1:多の関係(has many, belongs to)でも大丈夫ですが、viewで取得するとき少しめんどくさいです。

WS000259

ちなみに、hasOneメソッドで取得できるのは HasOne クラスで、親クラスはRelationクラスです。

Relation クラスでは マジックメソッドで \Illuminate\Database\Eloquent\Builder クラスのメソッドを呼んでるので、続いてクエリビルダーで書けるってわけですね。

WS000245

WS000246  WS000247  WS000248 WS000249

ちょっと寄り道しましたね。

今は 「リレーションを設定する」「リレーション時に発行するSQLを書く」の途中です。

 

hasOneメソッドで (クラス名, 結合先テーブルのカラム(test_user_item.user_id), 結合元テーブルのカラム(test_user.user_id)) を指定して、

「FROM test_user LEFT JOIN test_user_item ON test_user.user_id = test_user_item.user_item」を作ります。

hasOneメソッド中ではテーブル名は不要です。

 

あとは作りたいSQLに対してクエリビルダーを書いて、クエリビルダーをTestUserItemメソッドの中でreturn するだけなんですが、注意点が一つ。

SELECT に指定するフィールドに 「JOIN時に用いるカラム」を設定しなければいけません。

下の赤丸で囲んだ部分ですね。

WS000257

これはeager loading の機能で、取得した結果から user_id を取得して IN句を作ってるからだと思います。具体的な処理は見ていませんが。。。

なお、groupBy と selectRawを書いてる場所を分けてるのは特に意味はないです。全部繋げて書いて大丈夫です。

 

3. Model::withメソッドでeager loading設定

上で設定した 「リレーション先取得SQL」をeager loadingする設定です。

WS000260

with メソッドの引数に対応するリレーションを表すメソッド名(TestUserItem)を書きます。

これでOK。

コントローラとビューも見てみましょう。

WS000250 WS000262 WS000251

WS000261

ちゃんと取得できていますね。SQLも正しく発行されています。

ただ、view 側で気をつけなくてはいけないのは、「該当するリレーション先が存在しない場合があるときは、存在チェックを入れないといけない」ってことです。

@if(isset($each_result->TestUserItem))の部分ですね。

 

今回で言えば、user_id = 2 (shinoda)は、何もアイテムを持っていませんでした。

この場合は$each_result->TestUserItem が未定義になってしまうので、$each_result->TestUserItem->max_resultを取得しようとしたときにエラーになります。

ここだけ注意です。

 

実際 eager loading を使う場面は 「普通にjoinできない または join時の条件が複雑でindexが利かせにくい」って時だと思います。

join先のテーブルが違うDBにあるとか、今回みたいに対応するレコードのうち、

「一番大きい, 一番新しい」だけ欲しいって時ですね。

履歴系テーブルの最新情報だけ欲しいみたいなときにいいかもしれないです。

 

では。