Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column

Question

The application is hosted in Bluehost in a Shared hosting. I tried executing the below query in phpMyAdmin and the results works fine:

SELECT employee_id , floor(Datediff(max(to_date), min(from_date))/365) As diif FROM `work_experiences`  group by employee_id having floor(Datediff(max(to_date), min(from_date))/365) >=15

Below is my Laravel Query builder:

 $query->groupBy('employee_id');
 $query->havingRaw('Floor(Datediff(Max(to_date), min(from_date))/365) >='.$filterOverralExperienceA);
 $query->havingRaw('Floor(Datediff(Max(to_date), min(from_date))/365) <='. $filterOverralExperienceB);

The above code gives me the blow error:

Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'affixpro_jobhut.work_experiences.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (SQL: select count(*) as aggregate from users where is_active = 1 and exists (select * from user_types where users.id = user_types.user_id and is_type_active = 1 and is_active = 1) and exists (select * from employees where users.id = employees.user_id and is_edited_first_time = 1 and exists (select * from work_experiences where employees.id = work_experiences.employee_id group by employee_id having Floor(Datediff(Max(to_date), min(from_date))/365) >=10 and Floor(Datediff(Max(to_date), min(from_date))/365) <=100)))

Group by ID works fine but not employee_id.

I have already tried changing the database config strict to false but still this continues.

Appreciate your help and assistance in this.

Answer 1

In SQL when a query contains a GROUP BY clause, all columns returned by the query (in the SELECT part) must be included in the GROUP BY clause itself or aggregated so that MySQL knows which value it has to return.

So the reason of the error message is because the query generated by Eloquent contains (select * from work_experiences.

Now to fix it in your case, I guess that you only have to add $query->select('employee_id'); right before the GROUP BY clause so that the new query will include (select employee_id from work_experiences.