PHP
SQL
QueryBuilder
laravel5.6

LaravelのCountとGroupByでハマった話

やりたかったこと

特定のデータを年月単位で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
GOUP 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')

追記

2018/05/10現在 Laravel 5.6でwhereBetween()が下記の現象により、
生成クエリが where "tweeted_at" between ? and ? になるかもしれません。。。
whereBetween and DB::raw() bug · Issue #17810 · laravel/framework