laravel leftjoin query with selectraw and groupby not excuting

Question

this my customer table

this my booking details table

i need to run laravel query to get all customers with max date in booking details and if the customer has no booking detaisl i get customer with null date value i tried below query i tried below query

    $allcustomers=Customer::
        leftjoin('bookingdts','bookingdts.customer_id','=','customers.id' )
        ->groupBy('customers.id')
        ->selectRaw('customers.*,max(bookingdts.date) as maxdate')
        ->get();
but i get the below error
SQLSTATE[42000]: Syntax error or access violation: 1055 'elwady.customers.name' isn't in GROUP BY

Answer 1

using groupBy and after use an aggregate function like MAX may cause an error or unexpected result, so i suggest you try this insead :

$subquery = BookingDt::select('customer_id', DB::raw('MAX(date) as maxdate'))
    ->groupBy('customer_id')
    ->toSql();

$allcustomers = Customer::leftJoin(DB::raw("($subquery) as booking_maxdates"), 'booking_maxdates.customer_id', '=', 'customers.id')
    ->select('customers.*', 'booking_maxdates.maxdate')
    ->get();