[SOLVED] Fighting again with only_full_group_by!

Need Help
1

Hello,

I’m still wondering how to solve the ‘only_full_group_by’ problem building the queries with CakePHP.
I regularly solve it setting this flag off in mysql but I don’t know why it set it back on by itself.

Anyway, I’d like to understand how to fix it.

I again got it today with the following query:

$discoveryBooks = $this->DiscoveryBooks->find('all')
->contain([
    'DiscoveryCards'
])
->matching('DiscoveryCards', function ($q) use ($sitesIds) {
    return $q
    ->where([
        'DiscoveryCards.site_id IN' => $sitesIds
         
    ]);
})
->group(['DiscoveryBooks.id'])
->where([
    'published' => true,
    'approved' => true
])
->all();

Where it complains about DiscoveryCards.id which is not aggregated…

Well, according to mysql doc and Cake doc, I tried to do the following:

$discoveryBooks = $this->DiscoveryBooks->find('all')
->contain([
    'DiscoveryCards'
])
->matching('DiscoveryCards', function ($q) use ($sitesIds) {
    return $q
    ->select([
        'ANY_VALUE(id)' => 'id',
        'site_id',
        'discovery_book_id',
        'name',
        'QR_code',
        'sequence',
        'latitude',
        'longitude',
        'image',
        'copyright',
        'directory_name',
    ])
    ->where([
        'DiscoveryCards.site_id IN' => $sitesIds
         
    ]);
})
->group(['DiscoveryBooks.id'])
->where([
    'published' => true,
    'approved' => true
])
->all();

But in that case it complains about id which is ambiguous.

So I tried:

    ->select([
        'ANY_VALUE(DiscoveryCards.id)' => 'DiscoveryCards.id',

or

    ->select([
        'ANY_VALUE(id)' => 'DiscoveryCards.id',

or

    ->select([
        'ANY_VALUE(DiscoveryCards.id)' => 'id',

It always complains about a syntax error!

[PDOException] SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.`id`, DiscoveryCards.site_id AS `DiscoveryCards__site_id`, DiscoveryCards.disco' at line 1

Could you please help me to solve this problem?

2

Why do you use contain and matching for the same model?

3

I have this issue in a search form that contain too many fields, to solve it:
In your SQL run:
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));

4

It looks to me like ANY_VALUE(*) IS being escaped.

5

Ok @lucasthibau but it’s not what I’m looking for as sql_mode is regularly set to defaut value on my server and I don’t find why.
Second reason is that it’s not the expected behavior of mysql 5.7

6

OK @xaphalanx Could you please elaborate because I already see using ANY_VALUE() in the mysql doc but I don’t see how to use it here.

7

@rrd, because if I don’t specify contain(), I don’t get the associated data and I don’t see how can I write this request differently.
But I’m curious…

8

share your database table definitions for these 2 models

9

Ok rrd:

class DiscoveryBooksTable extends Table {

public function initialize(array $config) {
    parent::initialize($config);
    
    $this->displayField('name');
	$this->primaryKey('id');
	
	$this->belongsTo('Users');
	
	$this->hasMany('DiscoveryBooksLanguages',
	    [
	      'dependent' => true,
		  'cascadeCallbacks' => true
	    ]);
	
	$this->hasMany('DiscoveryCards',
	    [
	      'dependent' => true,
		  'cascadeCallbacks' => true
	    ]);

    }
}

And

class DiscoveryCardsTable extends Table {

public function initialize(array $config) {
    parent::initialize($config);
    
    $this->displayField('name');
	$this->primaryKey('id');
	$this->addBehavior('UpdateBookDate');
	
	$this->belongsTo('Sites');
	$this->belongsTo('DiscoveryBooks');
	
	$this->hasMany('DiscoveryCardsLanguages',[
	    	'dependent' => true,
	        'cascadeCallbacks' => true]);

    }
}
10

Share the corresponding SQL also.

11

@rrd
SQL tables structures:

CREATE TABLE `discovery_books` (
`id` int(11) NOT NULL,
`created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`user_id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`image` varchar(255) DEFAULT NULL,
`copyright` varchar(255) DEFAULT NULL,
`directory_name` varchar(255) NOT NULL,
`published` tinyint(11) NOT NULL DEFAULT '0',
`approved` tinyint(4) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `discovery_cards` (
  `id` int(11) NOT NULL,
  `site_id` int(11) DEFAULT NULL,
  `discovery_book_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `QR_code` varchar(255) DEFAULT NULL,
  `sequence` int(11) NOT NULL,
  `latitude` float NOT NULL,
  `longitude` float NOT NULL,
  `image` varchar(255) DEFAULT NULL,
  `copyright` varchar(255) DEFAULT NULL,
  `directory_name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
12

only full group by is the default setting for MySQL 5.7 and above

In your case the easiest soulution is to call select() and group() methods on the query with the same filed list.
So you have to end up with something like this.

$discoveryBooks = $this->DiscoveryBooks->find()
  ->select(['DiscoveryBooks.id', 'DiscoveryBooks.name', 'DiscoveryCards.name'])
  ->matching('DiscoveryCards', function ($q) use ($siteIds){
            return $q->where([
                'DiscoveryCards.site_id IN' => $siteIds
            ]);
        })
  ->group(['DiscoveryBooks.id', 'DiscoveryBooks.name', 'DiscoveryCards.name'])
  ->where([
            'published' => true,
            'approved' => true
        ])
  ->all();
13

@rrd,

Thanks a lot, that’s exactly the answer I was expecting.

Alain