noblemfd
1 month ago
242
6
Laravel

production.ERROR: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of ORDER BY clause is not in GROUP BY in Ubuntu

Posted 1 month ago by noblemfd

This code was working perfectly on my local system,:

     $all_leave_record = HrLeaveRequest::select(DB::raw("COUNT(leave_status) as count"), DB::raw("MONTHNAME(created_at) as month"))
        ->where('company_id', $userCompany)
        ->whereYear('created_at', date('Y'))
        ->orderBy(DB::raw("MONTH(created_at)"), 'ASC')
       ->groupBy(DB::raw("MONTHNAME(created_at)"))
        ->get();
    $all_leave_data = [];

    foreach($all_leave_record  as $row) {
       $all_leave_data['leave_label'][] = $row->month;
       $all_leave_data['all_leave_data'][] = (int) $row->count;
     }
     $all_leave_data['all_leave_chart_data'] = json_encode($all_leave_data); 

        $approved_leave_record = HrLeaveRequest:: select(DB::raw("COUNT(leave_status) as count"), DB::raw("MONTHNAME(created_at) as month"))
             ->where('company_id', $userCompany)
        ->where('leave_status', '=', 4)
        ->whereYear('created_at', date('Y'))
        ->orderBy(DB::raw("MONTH(created_at)"), 'ASC')
       ->groupBy(DB::raw("MONTHNAME(created_at)"))
        ->get();
    //    dd($approved_leave_record);
    $approved_leave_data = [];

    foreach($approved_leave_record  as $row) {
       $approved_leave_data['leave_label'][] = $row->month;
       $approved_leave_data['approved_leave_data'][] = (int) $row->count;
     }
   $approved_leave_data['approved_leave_chart_data'] = json_encode($approved_leave_data); 
   
     //Rejected Leaves          
    $rejected_leave_record = HrLeaveRequest::select(DB::raw("COUNT(leave_status) as count"), DB::raw("MONTHNAME(created_at) as month"))
        ->where('company_id', $userCompany)
        ->where('leave_status', '=', 3)
        ->whereYear('created_at', date('Y'))
        ->orderBy(DB::raw("MONTH(created_at)"), 'ASC')
       ->groupBy(DB::raw("MONTHNAME(created_at)"))
        ->get();
    
    $rejected_leave_data = [];

    foreach($rejected_leave_record  as $row) {
       $rejected_leave_data['leave_label'][] = $row->month;
       $rejected_leave_data['rejected_leave_data'][] = (int) $row->count;
     }
   $rejected_leave_data['rejected_leave_chart_data'] = json_encode($rejected_leave_data); 

but when I deployed to Ubuntu 18 server, I got this error:

production.ERROR: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'hr_leave_requests.created_at' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (SQL: select COUNT(leave_status) as count, MONTHNAME(created_at) as month from hr_leave_requests where company_id = 1 and year(created_at) = 2020 group by MONTHNAME(created_at) order by MONTH(created_at) asc) {"userId":2,"exception":"[object] (Illuminate\Database\QueryException(code: 42000): SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'hr_leave_requests.created_at' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (SQL: select COUNT(leave_status) as count, MONTHNAME(created_at) as month from hr_leave_requests where company_id = 1 and year(created_at) = 2020 group by MONTHNAME(created_at) order by MONTH(created_at) asc) at /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php:664, Doctrine\DBAL\Driver\PDOException(code: 42000): SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'hr_leave_requests.created_at' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by at /var/www/html/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:63, PDOException(code: 42000): SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'hr_leave_requests.created_at' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by at /var/www/html/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:61)

How do I get it resolved.

Thank you

Please sign in or create an account to participate in this conversation.