CakePHP3 and mySQL 5.7, fighting again with $*£*%ù only_full_group_by mode

1

Hello,

I obviously not fully understood what this ‘hot potatoe’ flag implies in our requests.

@rrd, really helped me here, but I again found the problem with the below request.

It seems that I have to list in the group() statement all fields I list in select().
I tried but I still get the error.

    $unlocked_sites = $this->Agthemes->find()
    ->select([
        'id',
        'site_id',
        'audioguide_id',
    ])
->contain([
    	'DeviceConnections' => function ($q) {
	       return $q
       		->select(['id', 'agtheme_id', 'request'])
            ->where(['request' => 'unlock']);
	    }
    ])
    ->matching('DeviceConnections', function ($q) {
        return $q
        ->select(['id', 'agtheme_id', 'request'])
        ->where(
            [
                'request' => 'unlock',
            ]);
    })
    ->distinct('agtheme_id')
    ->group([
        'site_id', 'id', 'audioguide_id', 'DeviceConnections.id','DeviceConnections.agtheme.id', 'DeviceConnections.request'
    ])
    ->toArray();

Error is:
2017-08-21 10:27:13 Error: [PDOException] SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘ndguide.DeviceConnections.id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Hum well, subsidiary question: Is it a good choice to run mySQL 5.7 with current CakePHP 3 version??

Alain

2

What is the full SQL that is being run? CakePHP 3 runs perfectly fine with MySQL 5.7, you just need to take into account where it’s strictness is. Would be able to help with the full query :slight_smile:

3

With full group bi restrictions you can take a 2 stepped approach; in the first step you do the group by (with joins as needed) and in the second you use the unique id’s from the first to contain and return all the data. This approach will work with pagination too.

4

@xaphalanx, Do you mean I have to perfom my request in 2 steps?

5

@dakota You want to know the performed requests?
Unfortunately I don’t know, when I try to use DebugKit, I only get a blak screen and I don’t know how to solve it on my server…

6

Yes make 2 queries. Page with just ids coming back and do a find with $exp->in($ids);