Syntax error or access violation: 1055 Expression #17 in group by

Question

I tried a query using group in laravel 5.3.I caught

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #17 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'testtravel.country.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (SQL:

select  `travel_request`.*, `travel_request`.`id` as `travel_id`,
        `department`.`name` as `dept_name`, `users`.`firstname` as `approver_name`,
        `travel_purpose`.`purpose`, `country`.`name` as `country_name`,
        `traveling_details`.`from_date`, `traveling_details`.`to_date`,
        `travel_request_status`.`status`
    from  `travel_request`
    inner join  `department`  ON `travel_request`.`department_id` = `department`.`id`
    inner join  `users`  ON `travel_request`.`approver_id` = `users`.`id`
    inner join  `travel_purpose`  ON `travel_request`.`travel_purpose_id` = `travel_purpose`.`id`
    inner join  `traveling_details`  ON `travel_request`.`id` = `traveling_details`.`travel_request_id`
    inner join  `country`  ON `country`.`id` = `traveling_details`.`country_id`
    inner join  `travel_request_status`  ON `travel_request`.`status_id` = `travel_request_status`.`id`
    where  `travel_request`.`approver_id` = 187
      and  `travel_request`.`status_id` != 4
    group by  `travel_request`.`id`
    limit  2 offset 0)

I copied the query and run in sql.Its is working well in mysql.I tried as

 $users = DB::table('travel_request')
        ->join('department', 'travel_request.department_id', '=', 'department.id')
        ->join('users', 'travel_request.approver_id', '=', 'users.id')
        ->join('travel_purpose', 'travel_request.travel_purpose_id', '=', 'travel_purpose.id')
        ->join('traveling_details', 'travel_request.id','=','traveling_details.travel_request_id' )
        ->join('country','country.id', '=', 'traveling_details.country_id')
        ->join('travel_request_status','travel_request.status_id', '=', 'travel_request_status.id')
        ->select('travel_request.*', 'travel_request.id as travel_id','department.name as dept_name','users.firstname as approver_name','travel_purpose.purpose','country.name as country_name','traveling_details.from_date','traveling_details.to_date','travel_request_status.status')->where('travel_request.approver_id', $user_id)->where('travel_request.status_id','!=','4')->GROUPBY ('travel_request.id')->paginate(2);

Solved by

However to disabled this just go to config/database.php and change strict flag

'mysql' => [
            .
            .
            .
            'strict' => false,
            //'strict' => true,
            .
            .
        ],

Answer 1

Open your config/database.php file and change mysql configuration array as following:

from 'strict' => true

to 'strict' => false

Do read this answer for more information about the error.

Answer 2

You are not using an aggregation function so could be you are using group by for ordering the result (or for obtain distinct value) starting for mysql 5.6 the use of group by only specifing the all the column involved for group and is not possible a partial specification.

The use of group by for ordering is depreacted also

so if you are using for ordering use a correct order by instead of group by

  select 
        `travel_request`.*
      , `travel_request`.`id` as `travel_id`
      , `department`.`name` as `dept_name`
      , `users`.`firstname` as `approver_name`
      , `travel_purpose`.`purpose`
      , `country`.`name` as `country_name`
      , `traveling_details`.`from_date`
      , `traveling_details`.`to_date`
      , `travel_request_status`.`status` 
  from `travel_request` 
  inner join `department` on `travel_request`.`department_id` = `department`.`id` 
  inner join `users` on `travel_request`.`approver_id` = `users`.`id` 
  inner join `travel_purpose` on `travel_request`.`travel_purpose_id` = `travel_purpose`.`id` 
  inner join `traveling_details` on `travel_request`.`id` = `traveling_details`.`travel_request_id` 
  inner join `country` on `country`.`id` = `traveling_details`.`country_id` 
  inner join `travel_request_status` on `travel_request`.`status_id` = `travel_request_status`.`id` 
  where `travel_request`.`approver_id` = 187 and `travel_request`.`status_id` != 4 
  order by `travel_request`.`id` limit 2 offset 0)

this behavior is controller by sql_mode=only_full_group_by you could change this param for use the old behavior https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

Answer 3

that is Solution and worked properly :) Open Terminal and

sudo mysql -uroot -ppassword

And change the SQL Mode for your MySQL Server Instance:

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));