The code below works fine but when I copied the whole cakephp3 project with the same DB I get the below error. Since the same code works elsewhere I am unsure on how to fix this. I am using latest Wamp64, win10. The rest of the cakephp3 project works fine. The other computer used wamp64 and win7.
A solution is there but this involves changing the DB settings and i preferred to change the code.
Error related to only_full_group_by when executing a query in MySql
Error: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #11 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'aptutori_test.Lessons.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
$options['fields'] = array('DISTINCT Student.* ','Guardian.* ');
$options['conditions'] = array('Lessons.tutor_id' => $tutorId,'Student.student_inactive'=>0,
'Lessons.lesson_date >='=> $currDate, 'Lessons.makeup_lesson' => 0,'Lessons.forefit' => 0);
$fields = [
'Student.first_name','Student.last_name','Student.id',"Student.address_street","Student.address_suburb","Student.address_postcode","Student.class_year",
"Guardian.id",'Guardian.guardian_first_name',"Guardian.guardian_last_name"
];
$query = $this->Lessons->find('all');
$students = $query->where($options['conditions'])->autoFields(true)
->select($fields)->distinct(["Student.id",'Guardian.id'])
->join($options['joins'])
;
$this->set( 'student',$students);
Here is the code which can get around the issue without changing the DB setting.
$students = $this->Lessons->find()
->contain([
'Students',
'Students.Guardians'])
->select([
'Students.last_name',
'Students.first_name',
'Students.id',
'Lessons.student_id',
'Lessons.tutor_id',
'Students.has_credit',
'Lessons.student_id',
'Lessons.id',
'Students.class_year',
'Lessons.subject_id',
'Guardians.guardian_last_name',
'Guardians.guardian_first_name',
'Students.address_street',
'Students.address_suburb',
'Students.address_postcode',
'Guardians.id'])
->where([
'Lessons.tutor_id' => $tutorId,
'Students.student_inactive' => 0,
'Lessons.lesson_date >=' => $currDate,
'Lessons.cancelled_lesson' => 0,
'Lessons.forefit' => 0])
->order([
'Lessons.lesson_date' => 'ASC',
'Lessons.start_time' => 'ASC'])
->hydrate(true);
$gdata = array();
$studentIds = array();
foreach ($students as $i => $item) {
if (in_array($item['student']['id'], $studentIds)) {
continue;
} else {
array_push($studentIds,$item['student']['id']);
$gdata[$i]['student']['id'] = $item['student']['id'];
$gdata[$i]['student_id'] = $item['student_id'];
$gdata[$i]['student']['first_name'] = $item['student']['first_name'];
$gdata[$i]['student']['last_name'] = $item['student']['last_name'];
$gdata[$i]['student']['guardian']['guardian_first_name'] = $item['student']['guardian']['guardian_first_name'];
$gdata[$i]['student']['guardian']['guardian_last_name'] = $item['student']['guardian']['guardian_last_name'];
$gdata[$i]['student']['address_street'] = $item['student']['address_street'];
$gdata[$i]['student']['address_suburb'] = $item['student']['address_suburb'];
$gdata[$i]['student']['address_postcode'] = $item['student']['address_postcode'];
$gdata[$i]['student']['class_year'] = $item['student']['class_year'];
}
}