Add MySQL function DATE_FORMAT to Symfony Doctrine DQL

Add MySQL function DATE_FORMAT to Symfony Doctrine DQL

To add MySQL function DATE_FORMAT() to Symfony Doctrine DQL, just create a file src\Acme\AcmeBundle\DQL\DateFormatFunction.php with:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

namespace Acme\Bundle\AcmeBundle\DQL;

use Doctrine\ORM\Query\Lexer;
use Doctrine\ORM\Query\AST\Functions\FunctionNode;
use Doctrine\ORM\Query\SqlWalker;
use Doctrine\ORM\Query\Parser;

/**
* Class DateFormatFunction
*
* Adds the hability to use the MySQL DATE_FORMAT function inside Doctrine
*
* @package Vf\Bundle\VouchedforBundle\DQL
*/
class DateFormatFunction extends FunctionNode
{

  /**
  * Holds the timestamp of the DATE_FORMAT DQL statement
  * @var $dateExpression
  */
  protected $dateExpression;

  /**
  * Holds the '% format' parameter of the DATE_FORMAT DQL statement
  * var String
  */
  protected $formatChar;

  public function getSql( SqlWalker $sqlWalker )
  {
    return 'DATE_FORMAT (' . $sqlWalker->walkArithmeticExpression( $this->dateExpression ) . ',' . $sqlWalker->walkStringPrimary( $this->formatChar ) . ')';
  }

  public function parse( Parser $parser )
  {
    $parser->Match( Lexer::T_IDENTIFIER );
    $parser->Match( Lexer::T_OPEN_PARENTHESIS );

    $this->dateExpression = $parser->ArithmeticExpression();
    $parser->Match( Lexer::T_COMMA );

    $this->formatChar = $parser->ArithmeticExpression();

    $parser->Match( Lexer::T_CLOSE_PARENTHESIS );
  }
}

and in your config.yml you can add:

1
2
3
4
5
6

orm:
  auto_generate_proxy_classes: %kernel.debug%
  auto_mapping: true
  dql:
    datetime_functions:
      DATE_FORMAT: Acme\Bundle\AcmeBundle\DQL\DateFormatFunction

and now you can use it like this:

1
2
3
4

$qb = $repo->createQueryBuilder('R')
  ->where( "DATE_FORMAT(R.createdAt, '%Y-%m-%d') = '2014-10-10'" )
  ->orderBy( 'R.id', 'ASC' )
  ->getQuery();