How to group by only year in DQL?-CodePudding

How can I translate this query to Doctrine Query Language

SELECT year(created), count(*) as newusers 
from `user` 
group by year(created);

CodePudding user response：

Since your query only needs to group by year from a datetime column, you will need the YEAR function, which is not supported by default in DQL. You can install DoctrineExtensions which will add the functionality you need.

composer require beberlei/DoctrineExtensions

And then edit the doctrine config file (config/packages/doctrine.yaml) as follow:

doctrine:
    orm:
        dql:
            string_functions: 
                YEAR: DoctrineExtensions\Query\Postgresql\Year

Now you will be able to write your request like this in your UserRepository

public function yearGroup(){
    return $this->createQueryBuilder('u')
        ->select('COUNT(u) as newusers, YEAR(u.created) as year')
        ->groupBy('year')
        ->getQuery()
        ->getResult();
}