- 2012/04/05

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’ :

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

// .........
// 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)
    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 :

// .........
// 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');
       ->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() ;)