LaravelのSQL文でJoinするときに片方のテーブルだけをWhereで絞り込みたい

前提・実現したいこと

SELECT shop.shop_name, sum(sales_data.sales_amount)
FROM sales_data
RIGHT OUTER JOIN shop ON shop.shop_id = sales_data.shop_id and sales_data.sales_date = '2018-09-01'
group by shop_name
というSQL文をLaravvelで実行したいのですがエラーが出てしまいます。
自分はsales.dataのテーブルをwhereで絞り込んだ後にshopのテーブルと結合したいんです。
上手い実行方法はありますでしょうか？
自分はサブクエリを生成して行ってるのですがこのやり方でいいので
しょうか？

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

SQLSTATE[42000]: Syntax error or access violation: 1103 Incorrect table name '[
{"shop_id"?,"sales_amount"?},{"shop_id"?,"sales_amount"?},{"shop_id"?,"sales_amount"?},{"shop_id"?,' (SQL: select `shop`.`shop_name` as `shop_name`, sum(sales_data) as sales_amount from `[{"shop_id":18,"sales_amount":15000},{"shop_id":18,"sales_amount":12963},{"shop_id":18,"sales_amount":12963},{"shop_id":18,"sales_amount":6032},{"shop_id":18,"sales_amount":16022},{"shop_id":18,"sales_amount":6667},{"shop_id":18,"sales_amount":12000},{"shop_id":18,"sales_amount":4915},{"shop_id":20,"sales_amount":25000},{"shop_id":20,"sales_amount":24160},{"shop_id":20,"sales_amount":24160},{"shop_id":20,"sales_amount":21926},{"shop_id":20,"sales_amount":21926},{"shop_id":20,"sales_amount":13880},{"shop_id":20,"sales_amount":5463},{"shop_id":20,"sales_amount":11663},{"shop_id":20,"sales_amount":11663},{"shop_id":20,"sales_amount":14850},{"shop_id":20,"sales_amount":7500},{"shop_id":20,"sales_amount":6667},{"shop_id":20,"sales_amount":21800},{"shop_id":20,"sales_amount":21800},{"shop_id":20,"sales_amount":21800},{"shop_id":20,"sales_amount":21800},{"shop_id":20,"sales_amount":6300}]` right join `shop` on `shop`.`shop_id` = `sales_data`.`shop_id` group by `shop`.`shop_name`)

該当のソースコード

Laravel
1$sub = Sales_data::select('shop_id', DB::raw('sales_data.sales_amount as sales_amount'))
2             ->where('sales_data.sales_date', '=', $date)
3             ->get();
4
5        $shops = DB::table($sub)->select('shop.shop_name as shop_name', DB::raw('sum(sales_data) as sales_amount'))
6               ->rightJOIN( 'shop', 'shop.shop_id', '=', 'sales_data.shop_id')
7               ->groupby('shop.shop_name')->get();

ここにより詳細な情報を記載してください。

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

回答1件

ベストアンサー

こんにちは。

下記でどうでしょうか。

php
1
2        $shops = Model::query()->select([
3            "shop.shop",
4            \DB::raw("sum(salse_data.sales_amount) as amount")
5        ])->rightJoin("shop", function(JoinClause $join) use($date){
6            $join->on("shop.shop_id", "=", "salses_data.shop_id")
7                ->where("sales_data.sales_date", $date);
8        })->groupBy("shop.shop_name");