-4

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);
Community
  • 1
  • 1
jagguy
  • 1
  • 3
  • 2
    Pay attention to the error messages. Read them. Google them if you don't understand their meaning: **this is incompatible with sql_mode=only_full_group_by** change that setting. But it's better to get the query work on most configurations and not to depend on specific settings. – floriank Dec 21 '16 at 00:29
  • I did google it and I am unsure how to fix this using cakephp3. You have an answer, please share it – jagguy Dec 21 '16 at 01:03
  • I said I did google it and with cakephp3 I am unsure what to do so why do i deserve to marked down 2 times. maybe if someone marks the question down give me a chance to fix up whatever is confusing them instead of no way back. – jagguy Dec 21 '16 at 01:46
  • I didnt want to change the DB but change the code. So how can I stop my questions frombeing voted down, what else do i need to do ? – jagguy Dec 21 '16 at 02:26
  • what is the massive issue with this question so i can fix this up or is someone just being mean? – jagguy Dec 21 '16 at 03:41
  • I have established that I preferred the cakephp code to be changed of which I did find a solution myself look below. After all this work I end up being banned from posting because of it. So it is a cakephp question but I ended up with 4 downvotes with no way back. So what do I do now about the downvotes? – jagguy Dec 22 '16 at 01:02
  • I did put the cakephp query with the error it created? I really dont get what I am supposed to be asking to avoid downvote nor do i get how to get the votes back – jagguy Dec 22 '16 at 01:04
  • You are missing the main issue. Instead of someone saying I find some part of the question unclear it is too late as it is voted down and no way to come back. Your explanation also indicates you were unclear of what was asked . This is not a problem but no further explanation is possible to retrieve an down votes and then the account gets cancelled and no further help is possible. – jagguy Dec 22 '16 at 22:45
  • I don't think I'm missing anything, you said you don't want to change the mysql config (good idea) and therefore the thing that needs to change is _the executed sql query_. People often remove or reverse their votes if the reason for giving them is addressed, but you haven't edited the question so they can't even if they want to. Im familiar with all of mysql, php and CakePHP - and I couldn't have answered your question. The tooltip for dv is "this question doesn't show any research effort; it is unclear, or not useful" this is the message _in addition_ to any specific comment if given. – AD7six Dec 23 '16 at 08:26

1 Answers1

0

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'];
    }
}
Stewartside
  • 20,378
  • 12
  • 60
  • 81
jagguy
  • 1
  • 3