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.

              

7 comments:

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

    ReplyDelete
  2. 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
  3. 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
  4. Wow! Such an amazing and helpful post this is. I really really love it. It's so good and so awesome. I am just amazed. I hope that you continue to do your work like this in the future also campingplatz holland mit hund

    ReplyDelete
  5. Another table may depict a request and have these segments: item, client, date, and deals cost. Change Site URL Using MySQL

    ReplyDelete
  6. blood pressure watch professional in R&D wearable blood pressure monitor, in the innovative form of a wrist watch, proactively monitors your heart health by turning real-time heart data into heart.
    mason jar lids canning lids supplier. offer canning Lids in different style and size
    Cervical spine massager We aim to create a healthy lifestyle that assists every individual to de-stress, relax. unwind and streamline a pain-free life and at the same time feel and look amazing.
    sex machine for her Choose Sex Machine to Get Orgasm and Enjoy Life|
    neon light for roomNeon Signs Light Is One-of-a-Kind Activities Designed and Hosted by Expert Locals. All Experiences are Vetted for Quality.

    ReplyDelete
  7. I would like to say that this blog really convinced me to do it! Thanks, very good post. www.excellentcustomclothing.com

    ReplyDelete

WhichIsBest
WhichIsBest