Edited at

LaravelのCountとGroupByでハマった話

More than 1 year has passed since last update.


やりたかったこと

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