LaravelのCountとGroupByでハマった話

カテゴリー
Laravel

やりたかったこと

特定のデータを年月単位でcountで集計するSQLを書きたい！

クエリビルダーで記述してみる

$queryBuilder = self::select(
   \DB::raw('to_char(created_at,\'YYYY/MM\') AS target_ym,
   COUNT(id) AS qty1,
   COUNT(DISTINCT hoge) AS qty2'
))
->whereBetween('created_at', $datetime)
->groupBy('target_ym')
->get();

この記述だと、下記のエラーのように
groupByにかけたいカラムが見つからないと言われてしまう。

[2018-05-10 19:32:34] local.DEBUG: SQLSTATE[42703]: Undefined column: 7 ERROR:  column "target_ym" does not exist
LINE 1: ...ts" where "tweeted_at" between $1 and $2 group by "target_ym...
                                                             ^
(SQL:
SELECT
   to_char(created_at,'YYYY/MM') AS target_ym,
   COUNT(id) AS qty1,
   COUNT(DISTINCT hoge) AS qty2
FROM "wood_round_table"
WHERE "created_at" BETWEEN 2018/04/01 00:00:00 AND 2018/04/30 23:59:59
GROUP BY "target_ym"
)

ええ・・SQLはあってるのに・・・

対応方法

groupByの中身も直接SQL記述したことにすると解決

$queryBuilder = self::select(
   \DB::raw('to_char(created_at,\'YYYY/MM\') AS target_ym,
   COUNT(id) AS qty1,
   COUNT(DISTINCT hoge) AS qty2'
))
->whereBetween('created_at', $datetime)
->groupBy(\DB::raw('target_ym'))
->get();

なぜ、こうなったのか

select内で通常の書き方をしたカラムや要素以外は、
そのままでは groupBy() で解決できないらしい。

例えば、ドキュメント(v5.6)のselectの例文で、下記のように記述してある場合は問題がないようです。

$users = DB::table('users')->select('name', 'email as user_email')