CODEWITHSUNDEEP
phpmysqllaravelgroup-by
user3386779
user3386779

Reputation: 7205

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

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,
            .
            .
        ],

Upvotes: 0

Views: 5357

Answers (3)

Mostafa Mahmoud
Mostafa Mahmoud

Reputation: 302

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

Upvotes: 0

Amit Gupta
Amit Gupta

Reputation: 17688

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.

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133400

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

Upvotes: 0

Related Questions