gpt4 book ai didi

php - Laravel Eloquent 查询

转载 作者:行者123 更新时间:2023-11-28 23:11:36 25 4
gpt4 key购买 nike

我有 3 个表,分别是 cash_managesoutletsdelivery_boys 结构是

        //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)

我想在 View 中得到如下结果(带送货员的现金)

num   outlet   delivery_boy   cash_taken  cash_returned  cash_has 
1 alabama John 1150 300 1050
2 alabama mike 500 0 500

我加入了所有三个表并能够获得Outlet NameDelivery Boy Name。现在我卡在了计算部分

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`)

我认为我的查询或逻辑有误,请帮助解决此问题

谢谢

最佳答案

这不是完整的答案,但很接近。不管我如何处理数据,我都无法弄清楚您是如何得出 cash_has 的。此外,我将其保留为原始 SQL,因为我觉得它会更有帮助,并且应该不难转换回查询生成器。我也不知道确切的列名,因此您可能需要修复其中的一些列名。

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)进行分组。

关于php - Laravel Eloquent 查询,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/45738926/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com