Technology Blog

Partners

WhichIsBest

Ads

ads
WhichIsBest

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.

              

9 comments:

  1. AGT The Chempions: I’m really happy to say it was an interesting post to read. I learned new information from your article, you are doing a great job. Continue -> AGT The Champions Winner

    ReplyDelete
  2. Thank you so much for posting this. I really appreciate your work. Keep it up. Great work!Best software training company with placement in Hyderabad

    ReplyDelete
  3. Thank you so much for posting this. I really appreciate your work. Keep it up. Great work!Best Placement company in Hyderabad

    ReplyDelete
  4. I am reading your post from the beginning,it was so interesting to read & I feel thanks to you for posting such a good blog,keep updates regularly..
    Web Designing and Development Training in Chennai | Web Designing Training Center in Velachery | Web Design Courses in Pallikaranai

    ReplyDelete
  5. Awesome post.. Really you are done a wonderful job.thank for sharing such a wonderful information with us..please keep on updating..
    PCB Designing Training Institute in Chennai | PCB Training Center in Velachery | PCB Design Courses in Thiruvanmiyur

    ReplyDelete
  6. Wonderful article..This is very informative blog. Glad to found your blog.Helps to gain knowledge about new concepts and techniques. Thanks for posting information in this blog..
    Linux Certification Training Institute in Chennai | Linux Training Center in Velachery | Linux Courses in Medavakkam

    ReplyDelete
  7. This is useful post for me. I learn lot of new information from your post. keep sharing. thank you for share us..
    Web Designing Training Institute in Chennai | Web Design Taining Center in Velachery | Web Designing Courses in Taramani

    ReplyDelete
  8. Thanks for sharing this great article! That is very interesting I love reading and I am always searching for informative articles like this.
    MatLab Training Institute in Chennai | MatLab Training Center in Velachery | Matlab Courses in Adyar

    ReplyDelete

WhichIsBest
WhichIsBest