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


I have just upgraded to MySQL 5.7, and according to the documentation ( 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()

Thank you for your help!

You will need to use the function builder.

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

Which will generate SQL like

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

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()
		    $type_link => 'any_value(' . $type_link . ')'

Looks terrible but works.