Creating Custom DQL MySQL Functions in Symfony2 with Doctrine

    Hi, this post is all about how to create and register doctrine custom DQL function with symfony. By default symfony with doctrine not allows some special functions like Cos, Sin, Acos, Atan, Date_diff, Concat etc.,

    But Doctrine allow us to write three different types of custom DQL functions such as datetime, numeric, string functions. so, this post guide you to take advantage over doctrine custom DQL functions with symfony.

    Ok, by simple three steps am gonna guide you to write your own DQL functions.

STEP 1: CREATING CUSTOM DQL FUNCTION CLASS:

        very first step is to write your own DQL function. Take example, am gonna write a DQL function for Acos() in mysql. The following is code for Acos function.

Acos.php

<?php
namespace DoctrineExtensions\Query\Mysql;
use Doctrine\ORM\Query\AST\Functions\FunctionNode,
      Doctrine\ORM\Query\Lexer;
class Acos extends FunctionNode
{
    public $arithmeticExpression;
    public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
    {
        return 'ACOS(' . $sqlWalker->walkSimpleArithmeticExpression(
                $this->arithmeticExpression
        ) . ')';
    }
    public function parse(\Doctrine\ORM\Query\Parser $parser)
    {
        $lexer = $parser->getLexer();
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        $this->arithmeticExpression = $parser->SimpleArithmeticExpression();
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }
}

        After creating Acos.php as above. Put above code into DoctrineExtensions\Query\Mysql folder in your symfony Bundle. For instance, folder structure is look as “ src\MyBundle\DoctrineExtensions\Query\Mysql”

Find below link to download different custom DQL functions:

https://github.com/beberlei/DoctrineExtensions/tree/master/lib/DoctrineExtensions/Query/Mysql

STEP 2: RESGISTER DQL CLASS IN DOCTRINE:

        After finishing up step 1, we have to tell Doctrine about the created custom DQL function class. For that write the following code in autoload.php.

Code:

$classLoader = new \Doctrine\Common\ClassLoader(
                        'DoctrineExtensions', __DIR__."/../src/MyBundle"
                        );
$classLoader->register();

STEP 3: REGISTER CUSTOM DQL FUNCTION IN SYMFONY:

        Final and most import step to use custom DQL function in symfony is to register the function with symfony. Write the following code in Symfony config.yml file. Find in “app/config/” folder.

Example:

doctrine:
    dbal:
            #your configuration
    orm:
        auto_generate_proxy_classes: "%kernel.debug%"
        auto_mapping: true
        dql:
                datetime_functions:
                          #path to date functions as same as numeric function
                numeric_functions:
                    acos: DoctrineExtensions\Query\Mysql\Acos
                string_functions:
                          #path to string functions as same as numeric function

        Thats all, we finish up writing our own DQL function and register it with symfony.

NOTE:

        DQL functions are categorized by three. Date functions, Numeric functions, String functions, the methods for writing functions are same but give some important while registering those functions in symfony configuration file.