Writing a Doctrine custom function
Unfortunately Doctrine does not support so many SQL function so you have to build a class to let him use a custom function. Let's start building a DATE_FORMAT function. Let's write the class:
namespace Application\Model\Doctrine\Functions;use Doctrine\ORM\Query\Lexer;use Doctrine\ORM\Query\AST\Functions\FunctionNode;/** * * DateFormat * * Allows Doctrine 2.0 Query Language to execute a MySQL DATE_FORMAT function * You must boostrap this function in your ORM as a DQLFunction. * * DATE_FORMAT(TIMESTAMP,'%format') : @link http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format * * @link labs.ultravioletdesign.co.uk * @author Rob Squires <rob@ultravioletdesign.co.uk> * */class DateFormat extends FunctionNode{ /** * holds the timestamp of the DATE_FORMAT DQL statement * @var mixed */ protected $dateExpression; /** * holds the '%format' parameter of the DATE_FORMAT DQL statement * @var string */ protected $formatChar; /** * getSql - allows ORM to inject a DATE_FORMAT() statement into an SQL string being constructed * @param \Doctrine\ORM\Query\SqlWalker $sqlWalker * @return void */ public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker) { return 'DATE_FORMAT(' . $sqlWalker->walkArithmeticExpression($this->dateExpression) . ','. $sqlWalker->walkStringPrimary($this->formatChar) . ')'; } /** * parse - allows DQL to breakdown the DQL string into a processable structure * @param \Doctrine\ORM\Query\Parser $parser */ public function parse(\Doctrine\ORM\Query\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->StringPrimary(); $parser->match(Lexer::T_CLOSE_PARENTHESIS); }}If you are working with ZF2 or Symfony2 framework, you will need to add the class to the entity manager:
$config = $em->getConfiguration();$config->addCustomDatetimeFunction('DATE_FORMAT', "\\Application\\Model\\Doctrine\\Functions\\DateFormat");$config->addCustomDatetimeFunction('MONTH', "\\Application\\Model\\Doctrine\\Functions\\Month");There are the methods addCustomStringFunction, addCustomNumericFunction, addCustomHydrationMode too. Remember to update the namespace of your classes.