Wednesday, December 31, 2014

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:


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.

              

7 comments:

  1. Thank you for this great tutorial. I really learned some new things in here. So thanks.

    school websites design

    ReplyDelete
  2. I said those information was wonderful and mySQL given information amazing,all peoples like that blog information,because of all information really very nice.
    sap basis training in chennai

    ReplyDelete
  3. All of these tips are great, that’s very interesting.but you would think if it were effective, more people would do it.I really appreciate the kind words.
    php training in chennai

    ReplyDelete
  4. About MYSQL functions information was really wonderful,you give another great work.It is more very helpful at the my studies.i can easily understand all given information.
    ios training in chennai

    ReplyDelete
  5. really amazing article here you had explained about the step by step process. So it will be easiest one for understanding the concepts.

    SEO Company in Chennai

    ReplyDelete
  6. Great site for these post and i am seeing the most of contents have useful for my Carrier.Thanks to such a useful information.Any information are commands like to share him.

    ccna training in chennai Adyar

    ReplyDelete
  7. This was so useful and informative. The article helped me to learn something new.

    iOS App Development Company

    ReplyDelete