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: 
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')

```
Very simple way:

1- Install this php lib : 

```
composer require beberlei/doctrineextensions

```

2- In doctrine.yaml add this extra config: 

```
doctrine:
    ...
    orm:
        ...
        dql:
            string_functions:
                MONTH: DoctrineExtensions\Query\Mysql\Month
                YEAR: DoctrineExtensions\Query\Mysql\Year
                DAY: DoctrineExtensions\Query\Mysql\Day
                ....................................

```

For more details, official docs is available here :
<https://packagist.org/packages/beberlei/doctrineextensions>