How to use MySQL 5.7 ANY_VALUE (GROUP BY (Aggregate) Functions)

Need Help
1

Hi!

I have just upgraded to MySQL 5.7, and according to the documentation (https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html) I have to options:

  • disable ONLY_FULL_GROUP_BY
  • use ANY_VALUE() function

Since I do not want escape the problem I would like to choose the second. My question is: How can I use ANY_VALUE() in CakePHP3 query builder? Could you please show me an example?

What I tried so far:
$categories = $this->find()
->select([‘category_link’])
->any_value([‘category_name’])
->group(‘category_link’);*/

Thank you for your help!

2

You will need to use the function builder.

$query = $this->find();
$query->select(['category_link' => $query->func()->any_value(['category_name'])])
  ->group('category_link');

Which will generate SQL like

SELECT (any_value(:c0)) AS category_link FROM categories GROUP BY category_link
3

Thank you for your reply. Unfortunately, I haven’t been able to make it work so I created this: (the variables has changed)

$sensors = $this->$model->find()
		->select([
		    $type,
		    $type_link => 'any_value(' . $type_link . ')'
		])
		->group([$type]);

Looks terrible but works.