Date SQL aggregate functions problem with doctrine and symfony

Hello GEEKs hope you are having a good day,

If you are a symfony developer you may have noticed that you’ll not be able to use sql Date functions (date, year, month, day) with doctrine. You’ll need to write an SQL query to do so and fall in the problem of mapping the results to entity. Actually i hate doing this.

While Googling i’ve found a doctrine extension which does that heavy task smoothly

which you can easily install via composer.json just add ‘”beberlei/DoctrineExtensions”: “dev-master’ to the required dependencies.

to use it you can just you the following snippet

public function getTodaysUsers($date) {

	$emConfig = $this->getEntityManager()->getConfiguration();  
	$emConfig->addCustomDatetimeFunction(‘YEAR’, ‘DoctrineExtensions\Query\Mysql\Year’);  
	$emConfig->addCustomDatetimeFunction(‘MONTH’, ‘DoctrineExtensions\Query\Mysql\Month’);  
	$emConfig->addCustomDatetimeFunction(‘DAY’, ‘DoctrineExtensions\Query\Mysql\Day’);

	$qb = $this->createQueryBuilder(‘e’)  
	->where("DAY(e.registeredAt) = :day")  
	->andwhere("MONTH(e.registeredAt) = :month")  
	->andwhere("YEAR(e.registeredAt) = :year");

	$qb->setParameter(‘year’, $date->format(‘Y’))  
	->setParameter(‘month’, $date->format(‘m’))  
	->setParameter(‘day’, $date->format(‘d’));

	return $qb->getQuery()->getResult();  

It’s an example for finding the today’s registered users. Only you’ll have to place this method to your Entity’s repository and here you are ready to go

Hope you’ve enjoyed,

Thanks 🙂