【Laravel】クエリビルダ：Select節内で加工したデータでグループ分け【#np2020】

つい何でもSQLをゴリゴリ書きそうになるところを抑えてクエリビルダで何とかしようと頑張るというのも、ORMもクエリビルダも使っていなかった環境からフレームワークに移行するときあるあるかなと思ったりするわけですが、例によって処理の中にこんな感じのクエリがありました。

SELECT
YEAR(a.datetime) as year
,AVG(a.temperature) as avg_temperature
FROM
amedas a
WHERE
MONTH(datetime) BETWEEN 3 AND 5
GROUP BY
year
;

amedasテーブルは毎日の気温を時間ごとに記録している巨大なテーブルです。そこから特定の季節（この場合は春）の平均気温を年ごとに集計しようっていう感じのクエリです。SQLで書くと別にどうってこと無いシンプルなクエリなんですが、これをクエリビルダで書こうと思って、、はてこれどうしたらいいのよ？

Select節内で加工したデータは？

ただ日付を取得するだけなら、

DB::select(datetime)->get();

で済みますが、あとで GROUP BY している関係上、加工して取得したい。当然だけどクエリビルダの中に YEAR() だの AVG() だのというのはない。どうしたもんか……

答え：諦めて DB::Raw() を使いましょう。

DB::select(
DB::raw('YEAR(datetime) as year')
,DB::raw('AVG(temperature) as avg_temperature')
)->get();

加工したデータに対してBETWEEN指定したいんだけど

通常であれば、whereBeteenを使えば実現出来ます。

DB::select(datetime)
->whereBetween(temparature,[10,20])
->get();

気温が10℃以上20度以下の日を取得します。でもこの「temparature」の部分を、日付の月にしたい。答えはもちろんこうでした。

答え：諦めて DB::Raw() を使いましょう。

諦めて DB::Raw() を使いましょう。

DB::select(datetime)
->whereBetween(DB::raw('MONTH(datetime)'),[3,5])
->get();

というわけで最初のクエリを置き換えるとこんな感じ

DB::select(
DB::raw('YEAR(datetime) as year')
,DB::raw('AVG(temperature) as avg_temperature')
)->whereBetween(DB::raw('MONTH(datetime)'),[3,5])
->groupBy('year')
->get();

いちおう動作確認はしたので大丈夫なはず。


DB::raw() ってなんでも出来ちゃって使いすぎると結局クエリビルダの意味ねーじゃねーかってなりそうだけど、この程度なら良いかなあと思います。仕方ないよね。クエリ全体をRawで渡すよりかはマシかなあ。

参考

Laravelクエリビルダーの集計関数で詰まった & MySQLの集計関数って変じゃない？ – YoshinoriN's Memento
php – Laravel Eloquent – Select MAX with other columns – Stack Overflow