CakePHP3: MySQL 5.7 problem (GROUP BY) with belongsToMany associations

Need Help
1

Hello,

The new ONLY_FULL_GROUP_BY is very very very boring problem I find almost everywhere in my code since I migrate to a new server.

I solved some queries thanks to helps found on Internet, but I have a problem solving the issue appearing on join tables:

Articles {
    'id',
    'name',
}

Categories {
    'id',
    'name'
}

ArticlesCategories {
    'article_id',
    'category_id'
}

And the query is:

$categories = $this->Categories->find()
->contain([
    'Articles' => function ($q) {
        return $q->distinct(['Articles.id']);
    }
])
->where([
    'Categories.name IN' => $themeNames,
->all();

The error is:

Exception: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'base.ArticlesCategories.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by in [/var/www/mySite/vendor/cakephp/cakephp/src/Database/Statement/MysqlStatement.php, line 36]

How to fix it please ?
Wouldn’t it be possible to automatically update the queries in the ORM core?
After all, that works perfectly disabling this *£$#£¤ option, so it could be possible to generate a ‘default’ GROUP BY assertion no? :confounded:

2

You need to go to the MySQL site and read about changes in group by.

3

Ok, I mean in ‘CakePHP’ language!

For example, the following CakePHP code:

$query = $this->Tcategories->find()
	->contain([
		'Sitecategories' => function ($q) {
			return $q->distinct(['Sitecategories.id']);
		}
	])
	->where([
	'LCASE(name) IN' => $objectThemes,
	'used' => true,
	'active' => true]);

Creates the following MySQL query:

SELECT TcategoriesSitecategories.tcategory_id AS `Sitecategories_CJoin__tcategory_id`, 
TcategoriesSitecategories.sitecategory_id AS `Sitecategories_CJoin__sitecategory_id`, 
TcategoriesSitecategories.id AS `Sitecategories_CJoin__id`, 
Sitecategories.id AS `Sitecategories__id`, 
Sitecategories.code AS `Sitecategories__code`, 
Sitecategories.field_id AS `Sitecategories__field_id`, 
Sitecategories.created AS `Sitecategories__created`, 
Sitecategories.modified AS `Sitecategories__modified`, 
Sitecategories.icon_name AS `Sitecategories__icon_name`, 
Sitecategories.name_fr AS `Sitecategories__name_fr`, 
FROM sitecategories Sitecategories 
INNER JOIN tcategories_sitecategories TcategoriesSitecategories 
ON Sitecategories.id = (TcategoriesSitecategories.sitecategory_id) 
WHERE TcategoriesSitecategories.tourinsoftcategory_id in (:c0) 
GROUP BY Sitecategories.id

Obviously, looking at the MySQL query, I guess I have to add TcategoriesSitecategories.id in GROUP BY clause, but I don’t see how to do it in CakePHP code.

Also, except to wait for the error to occur, it’s not easy to guess to add this group() clause in the query.

4

i think you should add group into find()
'group' => array("ModelName.fieldName",),

5

Ok but the problem here concerns the join table. I don’t talk about it in my query, CakePhp automatically creates this intermediate query so I don’t see how I can specify the group() statement.

6

You use group when you want to do a function or a special process on fields that you wanna…
example:
$this->User->find(‘all’,
‘fields’ => array(“COUNT(‘User.salary’) as sum_salary”),
‘group’ => array(‘User.salary’)
);

7

@anyway111: You are using CakePHP2.x-Syntax. The ORM in CakePHP3.x is quite different.

I am experiencing the same issues with ‘group by’ on MySQL 5.7. The only solution right now seems to deactivate the default behaviour in MySQL.

1 Like
8

Yep, And I feel very strange that we have no feedback from CakePHP team!

9

I am also facing same problem. Can CAKEPHP team help on this?