问题描述
我有3个表,分别为cash_manages
,outlets
和delivery_boys
,结构为
I have 3 tables called cash_manages
, outlets
, and delivery_boys
the structure is
//outlets
id name
1 utha
2 alabama
//delivery_boys
id outlet_id name
1 1 John
2 1 Mike
3 2 Alex
//cash_manage
id source_type source_id destination_id status amount
1 admin 1 2 give 500
2 admin 2 1 give 350
3 deliveryBoy 1 2 receive 300
4 admin 2 2 give 500
5 admin 2 1 give 800
6 user 1 1 give 600
7 user 2 2 give 450
//the logic
1-> if source_type is admin then the source_id is outlet_id and the cash is **GIVEN** to destination_type delivery_boy_id
2-> if source_type is deliveryBoy then the source_id is delivery_boy_id and the cash is **Received** by destination_type outlet_id(admin)
我想在视图中获得如下结果(带送货员的现金)
I want to get the result as below in the view (the cash with delivery boy)
num outlet delivery_boy cash_taken cash_returned cash_has
1 alabama John 1150 300 1050
2 alabama mike 500 0 500
我加入了所有三个表,并能够获得 插座名称 和送货男孩姓名.现在我被卡在计算部分了
I joined all three tables and able to get the Outlet Name and Delivery Boy Name.Now i am stuck at calculation part
public function index(Request $request)
{
$outlet_id = $request->outlet_id;
$transaction_list = DeliveryCashManage::leftJoin('outlets','outlets.id','delivery_cash_manages.source_id')
->leftJoin('delivery_boys','delivery_boys.id','destination_id')
->where('source_type', 'admin')
->where('source_id', $outlet_id)
->select('delivery_cash_manages.id','outlets.name as outlet','delivery_boys.name as delivery_boy','amount')
->groupBy('delivery_boys.name')
->get();
return view('pages.manager.cash');
}
我遇到以下错误
SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'ideal_chicken.delivery_cash_manages.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (SQL: select `delivery_cash_manages`.`id`, `outlets`.`name` as `outlet`, `delivery_boys`.`name` as `delivery_boy`, `amount` from `delivery_cash_manages` left join `outlets` on `outlets`.`id` = `delivery_cash_manages`.`source_id` left join `delivery_boys` on `delivery_boys`.`id` = `destination_id` where `source_type` = admin and `source_id` = 1 group by `destination_id`)
我认为我的查询或逻辑有误,请在解决此问题方面需要帮助
I think my query or logic is wrong, please need some help in fixing this
谢谢
推荐答案
这不是完整的答案,但是很接近.不管我如何处理数据,我都无法弄清楚您是如何提出cash_has
的.另外,我将其保留为原始SQL,因为我认为它会更有用,并且不难转换回查询生成器.我也不知道确切的列名,因此您可能必须修复其中一些.
This isn't the complete answer but it's close. No matter how I work the data though, I can't figure out how you are coming up with cash_has
. Also I'm keeping it raw SQL because I feel it would be more helpful and shouldn't be difficult to convert back to query builder. I also don't know the exact column names so you may have to fix some of those.
SELECT
COALESCE(outlets_admin.name, outlets.name) AS outlet,
COALESCE(boys_admin.name, boys.name) AS delivery_boy,
SUM(IF(cm.source_type = 'admin', amount, 0)) AS cash_taken,
SUM(IF(cm.source_type = 'deliveryBoy', amount, 0)) AS cash_returned,
SUM(IF(cm.source_type = 'admin', amount, 0)) - SUM(IF(cm.source_type = 'deliveryBoy', amount, 0)) AS cash_has
FROM delivery_cash_manages cm
LEFT JOIN outlets ON outlets.id = cm.destination_id AND cm.source_type = 'deliveryBoy'
LEFT JOIN delivery_boys boys ON boys.id = cm.source_id AND cm.source_type = 'deliveryBoy'
LEFT JOIN outlets outlets_admin ON outlets_admin.id = cm.source_id AND cm.source_type = 'admin'
LEFT JOIN delivery_boys boys_admin ON boys_admin.id = cm.destination_id AND cm.source_type = 'admin'
WHERE COALESCE(outlets.id, outlets_admin.id) = '2' # This is where you plug in your $outlet_id
GROUP BY outlet, delivery_boy
查询出错的原因是,如果按任何内容分组,则需要按选择的所有内容进行分组,这些内容不是聚合列(诸如sum,max,avg之类的函数).
The reason you are getting an error with your query though is if you group by anything, you need to group by everything you select which are aren't aggregate columns (functions like sum, max, avg).
这篇关于Laravel雄辩的查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!