Laravel eloquent query

Question

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');
}

i'm getting the following error

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

thank you

Answer 1

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

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