Laravel雄辩的查询 [英] Laravel eloquent query

问题描述

我有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雄辩的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！