How optimize query performance?

I have 1M+ records. The code below works, but it takes too long to load because I'm using get() and then filter based on request data. I tried using SQL groupBy() directly, but got a MySQL ''Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column'''. For this I do not want to change strict mode false in config . How can I make this faster? Any suggestion to optimize it?

   $orders = Order::with([ 
            'customer:id,email,country_id,name',
            'orderDetails:id,invoice_no, tx_id', 
            'customer.address:id,title, address', 
            'statusLog:order_id,created_at' 
        ])
        ->select('id','customer_id','requested_at','approved_at','status','amount',  'commission_amount', 'group_id', 'type',  'created_at') 
        ->whereNotNull('group_id')
          ->get();

    $grouped = $orders->groupBy('group_id')->map(function ($items) use ($csvGroupBy) { 
        $firstItem = $items->last(); // I need to get last item of group by 
        $firstItem->amount = $items->sum('amount');
        $firstItem->total_requested = $items->count();
        return $firstItem;
    })->values();

    if (isset($request['invoice_no']) && $request['invoice_no']) { 
        $grouped = $grouped->filter(function ($item) use ($request) {
            return $item->orderDetails && $item->orderDetails ->invoice_no== $request['invoice_no']; 
        });
    }


    if (isset($request['amount']) && $request['amount']) {
        $grouped = $grouped->filter(function ($item) use ($request) {
            return $item->amount == $request['amount'];
        });
    }

    $page = $request->get('page', 1);
    $perPage = $request->get('per_page', $perPage);
    $orders = new \Illuminate\Pagination\LengthAwarePaginator(
        $grouped->forPage($page, $perPage),
        $grouped->count(),
        $perPage,
        $page,
        ['path' => url()->current(), 'query' => $request->query()]
    );
   return $orders;