前提・実現したいこと
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件
あなたの回答
tips
プレビュー
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。