Pedro Luz Pedro Luz - 1 year ago 247
SQL Question

Doctrine querybuilder DATE_FORMAT not working

I'm having some problems with DATE_FORMAT inside a createQueryBuilder

My code:

$qb7Days = $repo->createQueryBuilder('R')
->select( 'R.createdAt' )
->where( "DATE_FORMAT(R.createdAt, '%Y-%m-%d') = :afterDays" )
->andWhere( 'R.cCurrentReviewState = :state' )
->andWhere( 'R.reminder = :reminder' )
->setParameter( 'afterDays', $after7Days )
->setParameter( 'state', $oReviewStateNotVerified ) // not_verified
->setParameter( 'reminder', 0 ) // never sent any reminder
->orderBy( '', 'ASC' )

But im getting

[Syntax Error] line 0, col 7: Error: Expected known function, got 'DATE_FORMAT'

I've searched some links and find some explain that it should work this way, but for me it looks like im doing something wrong.

Answer Source

DATE_FORMAT along with a bunch of Mysql function are not directly available in Doctrine.

To use them you can either create your own or add an extension like beberlei/DoctrineExtensions and then add the respective function to your doctrine bundle config like

                DATE_FORMAT: DoctrineExtensions\Query\Mysql\DateFormat