Laravelのクエリビルダが発行するSQLは正しいのに、Laravel上でエラーになってしまう。

Question

やりたいこと

サブクエリでschedule_user.attendanceの人数を数えてscheduleテーブルに外部結合をしたい。

環境

$ php --version
PHP 7.1.31 (cli) (built: Jul 31 2019 08:44:15) ( ZTS MSVC14 (Visual C++ 2015) x64 )
Copyright (c) 1997-2018 The PHP Group
Zend Engine v3.1.0, Copyright (c) 1998-2018 Zend Technologies

$ composer --version
Composer version 1.9.0 2019-08-02 20:55:32

$ php artisan --version
Laravel Framework 5.8.31

$ MySQL --version
Your MySQL connection id is 11
Server version: 5.7.27-log MySQL Community Server (GPL)

生成したいSQL（cmdで試したところ通ります）

select * from `schedules` left JOIN (
	select `schedule_id`, count(attendance) as user_count from `schedule_user` where `attendance` = 1)
as `schedule_user` on `schedules`.`id` = `schedule_user`.`schedule_id`

発生している問題・エラーメッセージ

SQLSTATE[42000]: Syntax error or access violation: 1140 In aggregated query without GROUP BY,
 expression #1 of SELECT list contains nonaggregated column 'clubs.schedule_user.schedule_id';
 this is incompatible with sql_mode=only_full_group_by 
(SQL: select * from `schedules` left join (
select `schedule_id`, count(attendance) as user_count from `schedule_user` where `attendance` = 1
) as `schedule_user` on `schedules`.`id` = `schedule_user`.`schedule_id`)

該当のソースコード

$count_query = \DB::table('schedule_user')
    ->select('schedule_id', \DB::raw('count(attendance) as user_count'))
    ->where('attendance', 1);

 $items = \DB::table('schedules')
    ->leftJoinSub($count_query, 'schedule_user', function($join) {
        $join->on('schedules.id', '=', 'schedule_user.schedule_id');})
    ->get();

Answer 1

SELECT の並びに カラム1 と集合関数(今回はcount)を使った場合は group by カラム1 と指定して、文法的に明確に カラム1毎の count を指定しなければなりません。

select * from `schedules` left JOIN (
    select `schedule_id`, count(attendance) as user_count from `schedule_user` where `attendance` = 1)
as `schedule_user` on `schedules`.`id` = `schedule_user`.`schedule_id`

             ↓

select * from `schedules` left JOIN (
    select `schedule_id`, count(attendance) as user_count from `schedule_user` where `attendance` = 1 group by `schedule_id`)
as `schedule_user` on `schedules`.`id` = `schedule_user`.`schedule_id`

Laravel SQLからクエリビルダーへの変換支援
も参考に。

Comments

[near108]

修正してみました。
SQLについては正しく動作しているのですが、今まで取れていたカラム名でデータを取得できなくなりました。Undefined propertyとエラーが表示されています。

[Orlofsky]

Laravel ってSQLのインラインビュー(今回のようにSELECTの中にSELECTがある) にきちんと対応できていないように思えます。
CREATE VIEW VIEW1 AS SELECT ...
でビューを作って、ビューを単純なテーブルのように取り込んでみては？

Answer 2

$join->on('schedules.id', '=', 'schedule_user.schedule_id');})

ここを

$join->on('schedules.id', '=', \DB::raw('schedule_user.schedule_id'));})

ここを
でどうでしょうか

おそらく、第三引数がエスケープ処理されているので.を含めたテーブル名と認識されているのだと思います。
また、同名のカラム名がなければ、テーブル名が省略できる気がするので

$join->on('schedules.id', '=', 'schedule_id');})

でも行けるかも・・・？

Comments

[near108]

確かにおっしゃる通りテーブル名は省略できますがエラー表示が変わることがなかったため、根本的な解決では無いかも？