Doctrine how to use ANY_VALUE mysql function

Question

Since mysql 5.7.5 group by have change

https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

If I make this query

$qb = $this->createQueryBuilder('d');
$qb->select('ANY_VALUE(d.id), d.nom, count(d.nom) as lignes');
$qb->groupBy('d.nom');   
$qb->orderBy('d.nom');

I have a error

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'sepultures.d0_.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

As explain in mysql doc, this problem can resolve with any_value like this

$qb = $this->createQueryBuilder('d');
$qb->select('ANY_VALUE(d.id), d.nom, count(d.nom) as lignes');
$qb->groupBy('d.nom');   
$qb->orderBy('d.nom');

But doctrine don't recognize this function

[2/2] QueryException: [Syntax Error] line 0, col 7: Error: Expected known function, got 'ANY_VALUE'

How can I resolve it?

Answer 1

as vincent said, I must create a function :

<?PHP
//src/MyBundle/DoctrineExtensions/Utils/AnyValue.php

namespace DoctrineExtensions\Utils;

use Doctrine\ORM\Query\AST\Functions\FunctionNode;
use Doctrine\ORM\Query\Lexer;
use Doctrine\ORM\Query\Parser;
use Doctrine\ORM\Query\SqlWalker;

class AnyValue extends FunctionNode {

    public $value; // la valeur  passée en paramètre de la fction ANY_VALUE()

    public function parse( Parser $parser ) {
        $parser->match( Lexer::T_IDENTIFIER ); //identifie la fonction ANY_VALUE() de mysql
        $parser->match( Lexer::T_OPEN_PARENTHESIS ); //parenthèse ouvrante
        $this->value = $parser->StringPrimary();
        $parser->match( Lexer::T_CLOSE_PARENTHESIS );////parenthèse fermante
    }

    public function getSql( SqlWalker $sqlWalker ) {
        return 'ANY_VALUE(' . $this->value->dispatch( $sqlWalker ) . ')';
    }
}

Declaration of doctrine extension class (with a key named ANY_VALUE) in the config.yml file:

#app/config/config.yml
doctrine:        
    orm:
        auto_generate_proxy_classes: %kernel.debug%
        default_entity_manager: default
        entity_managers:
            default:
                auto_mapping: true
                connection: default
                dql:
                    string_functions:
                        ANY_VALUE: DoctrineExtensions\Utils\AnyValue

the request:

//ANY_VALUE représente la clé contenue dans le fichier config.yml
$qb = $this->createQueryBuilder('d');
$qb->select('ANY_VALUE(d.id), d.nom, count(d.nom) as lignes');
$qb->groupBy('d.nom');   
$qb->orderBy('d.nom')