How To Select YEAR() MONTH() DAY() In Doctrine2

Doctrine2 is slightly different with Doctrine1. I’ve encountered an error when trying to do sql select YEAR() MONTH() and DAY() via Doctrine2. See error message below :

"[Syntax Error] line 0, col 110: Error: Expected known function, got 'YEAR'"

Unlike Doctrine1, Doctrine2 does not know what is YEAR() MONTH() and DAY() in DQL. Okay, lets see the showcase in detail. For example, I have a following MySQL table named ‘post’ :

CREATE TABLE IF NOT EXISTS `post` (
    `id_post` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `post_title` varchar(200) NOT NULL,
    `post_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id_post`),
    KEY `post_title` (`post_title`),
    KEY `post_date` (`post_date`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
 
INSERT INTO `post` ( `id_post` , `post_title` , `post_date` )
       VALUES ( NULL , 'Post Title', '2011-12-27 06:44:32' );

And I want an sql query like :

SELECT p.post_title
FROM   post AS p
WHERE  YEAR( p.post_date ) = 2011
AND    MONTH( p.post_date ) = 12
AND    DAY( p.post_date ) = 27

I cannot do this directly via DQL like in Doctrine 1. For simple comparison, let see what DQL I use in Doctrine1 to do previous SQL statement :

<?php
 
// .........
// Doctrine1 model class
// .........
public function findOneByYearMonthDay($year, $month, $day)
{
    $post = $this->getTable()->createQuery('p')
                 ->where('YEAR(p.post_date) = ?', $year)
                 ->andWhere('MONTH(p.post_date) = ?', $month)
                 ->andWhere('DAY(p.post_date) = ?', $day)
                 ->fetchOne();
    return $post;
}

For Doctrine2, you have to register your own function to recognize YEAR() MONTH() and DAY() in DQL. So what to do next ? I have found usefull sets of Doctrine Extensions, but they does not have YEAR() MONTH() and DAY(). But anyway, thanks to git (and github) because I can fork these repo and add my own functions. So lets do previous SQL statement in Doctrine2 way :

<?php
 
// .........
// Doctrine2 repository class for entity 'Post'
// .........
public function findOneByYearMonthDay($year, $month, $day)
{
    $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('p');
    $qb->select('p')
       ->where('YEAR(p.postDate) = :year')
       ->andWhere('MONTH(p.postDate) = :month')
       ->andWhere('DAY(p.postDate) = :day');
    
    $qb->setParameter('year', $year)
       ->setParameter('month', $month)
       ->setParameter('day', $day);
    
    $post = $qb->getQuery()->getSingleResult();
    return $post;
}

Yes, now Doctrine2 DQL recognize YEAR() MONTH() DAY() ;)