LarabelでデータクエリでIDが合致する平均値をJoinしてGetしたい。

Larabelでサーバを構築しています。

TableAとTableBがあり、★評価の数値をTablesBで管理していてTableAにはUser情報が入っています。
TableAにUser1が存在していた場合にTableBには評価が存在しない場合もあり、複数存在する場合もあります。
複数存在する場合は平均値を求めてGetしたいです。
TablesBにはdataで１から５の数値で入っているので、１と５なら平均の３をユーザ毎に出力したい場合のクエリの書き方をご教授ください。

Controllerでデータベースのクエリを以下のように記載していますが、エラーとなります。
どのように記載すべきかご意見をお願いします。

php
1DB::connection()->enableQueryLog();
2$s_word  = $request->input('s_word');
3$result = DB::table('table_a')
4    ->leftjoin('table_b', function ($join) {
5        $join->on('table_a.userid', '=', 'table_b.userid')
6             ->avg('data');
7    });
8$result = $result ->get();

php
1    "message": "SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to you
2r MySQL server version for the right syntax to use near 'on `table_a`.`userid` = ?' at line 1 (SQL: select avg(`data`) as aggregate on `table_a`.`
3userid` = table_b.userid)",

●table_a:ユーザテーブル

userID	Name
1	userA
2	userB

●table_b:評価テーブル

userID	Data
1	1
1	5

●期待するクエリ回答
1,userA,3
2,userB,null

aro10

2017/09/29 16:03

問題解決に重要なので、クエリエラーの詳細を追記してください。

yamayamak

2017/09/30 02:18

ありがとうございます。エラーを追記しました。よろしくお願いします。

t_obara

2017/09/30 07:44

そもそも、クエリビルダで構築する前に、取得できるsqlを考えて、それを置き換えるようにした方が考えやすいかと思います。期待するデータを得るためのsqlコマンドは理解されているのでしょうか？Noであるならば、そちらの方をお聞きになった方が、larabelを知らなくてもsqlについてはご存知の方からも回答が得られるやすくなるかと思います。Yesの場合は、sql文をクエリビルダで表現するにはどうしたらといった質問の方がわかりやすいので、これも回答が得られるやすくなるかと思います。

yamayamak

2017/09/30 08:03 編集

SQLでは以下のような感じで出来ています。「 select `userID`,`Name`, AVG(data) as r_avg from `table_a` left join `table_b` on `table_a`.`userid` = `table_a`.`userid` group by `userid`;」実際に実装するのはLaravelで実装しないと難しいので、Laravelでの実施の仕方を教えて欲しいです。できそうで出来ないところなのですが、出来ないのでしょうか？

t_obara

2017/09/30 08:34

そこまで既にご理解いただけているのであれば、 https://readouble.com/laravel/5.3/ja/queries.html この辺りが参考になるでしょう。既に回答も一部出ておりますが、回答は一つだけではないので、ご自身の好み・わかりやすさで選択されてはいかがでしょうか。できそうでできないという部分はもしかするとrawを利用するとよいと思います（前述のリンクに記載がありますが）。

行動規範の内容に同意します

回答1件

ベストアンサー

質問への追記の内容から、SQL文が既にできていてSQLをクエリビルダーに落とし込むのが大変な場合は、SQLを直接書く方法で対応してみてください。
Laravel 5.5 データベース：利用開始 SQLクエリの実行
DB:selectを使えば、PDOのような感覚でSQLがそのまま書けます。
(例)

$users = DB::select('select * from users where active = ?', [1]);

投稿2017/09/30 08:20

aro10

総合スコア4106

yamayamak

2017/09/30 08:46

ありがとうございます。Laravelで解決したかったのですが、LaravelのGroupByがしょぼい気がします。い1日いろいろやりましたが解決しませんでした。最終的には「'Name' isn't in GROUP BY・・・」となってGroupeByで指定しないフィールドがSelectできませんでした。SQLなら出来てるのに。。。SQLで書き換えるなら出来そうです。。。ありがとうございました。