0

I already encountered such problem but I don't understand how to define group() in this case:

I have the following tables:

Articles {
    'id',
    'name',
}

Categories {
    'id',
    'name'
}

ArticlesCategories {
    'article_id',
    'category_id'
}

And the query is:

$seconds = $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]

So as the problems seems to come from the join table, I don't see how to write the group() clause.

fralbo
  • 2,534
  • 4
  • 41
  • 73
  • this might help you..http://stackoverflow.com/questions/23921117/disable-only-full-group-by – Manohar Khadka Jan 01 '17 at 09:01
  • @ManoharKhadka that's a possibility but I would prefer to fix the problem in the query. – fralbo Jan 01 '17 at 09:56
  • I think that's not the problem with query..And may be it's not even issue with cakephp..Your query looks all good. – Manohar Khadka Jan 01 '17 at 11:29
  • @ManoharKhadka I don't know, I have this problem for all belongsToMany associations. I begin to think your proposal is maybe the best solution :-( – fralbo Jan 01 '17 at 11:33
  • which php version are you using ? and does that query work without contain or group by ? – Manohar Khadka Jan 01 '17 at 11:36
  • @ManoharKhadka I'm using php7.0.12 and mysql 5.7.16. All queries work except the ones using `distinct`, `order`, `count`, etc.. – fralbo Jan 01 '17 at 12:13
  • If your relations are all good then this might surely be the problem with mysql version..please just try this in other versions to be sure on that. – Manohar Khadka Jan 01 '17 at 13:53
  • @ManoharKhadka older versions work fine but this problem is well known with 5.7 – fralbo Jan 01 '17 at 15:22

0 Answers0