How to create and use custom built doctrine DQL function in symfony

03/08/2014 - DOCTRINE, SYMFONY

Doctrine doesn't provide all the database functions that we know by default however we can create our own function easily in symfony as shown below. The name of the function you're creating must exists database. e.g. There is no such function called hello() so you shouldn't try to create and use it. For more information, click here and here.


Function


We're going to create SHA1 function.


# sport/src/Football/FrontendBundle/DQL/Sha1.php
namespace Football\FrontendBundle\DQL;

use Doctrine\ORM\Query\AST\Functions\FunctionNode;
use Doctrine\ORM\Query\Lexer;
use Doctrine\ORM\Query\Parser;
use Doctrine\ORM\Query\SqlWalker;

class Sha1 extends FunctionNode
{
    public $value;

    public function parse(Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        $this->value = $parser->StringPrimary();
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }

    public function getSql(SqlWalker $sqlWalker)
    {
        return 'SHA1(' . $this->value->dispatch($sqlWalker) . ')';
    }
}

Register


# sport/app/config/config.yml
doctrine:
    orm:
        dql:
            string_functions:
                SHA1: Football\FrontendBundle\DQL\Sha1

Use


# sport/src/Football/FrontendBundle/Repository/CountryRepository.php
namespace Football\FrontendBundle\Repository;

use Doctrine\ORM\EntityRepository;
use Doctrine\ORM\Query;

class CountryRepository extends EntityRepository
{
    public function findOneByIdForRemoveSha1($id)
    {
        return
            $this
                ->createQueryBuilder('c')
                ->select('c, SHA1(c.code) AS code')
                ->where('c.id = :id')
                ->setParameter('id', $id)
                ->getQuery()
                ->getSingleResult(Query::HYDRATE_SCALAR);
    }
}


Test


/**
 * @Route("/country")
 */
class CountryController extends Controller
{
    /**
     * @Route("/delete/{id}", requirements={"id"="\d+"})
     * @Method({"GET"})
     */
    public function deleteAction($id)
    {
        $repo = $this->getDoctrine()->getRepository('FootballFrontendBundle:Country');

        $country = $repo->findOneByIdForRemoveSha1($id);

        echo '<pre>';
        print_r($country);
        exit;
    }
}

Result


Array
(
    [c_id] => 192
    [c_code] => DE
    [c_name] => Germany
    [c_createdAt] => DateTime Object
        (
            [date] => 2015-05-24 08:44:09.000000
            [timezone_type] => 3
            [timezone] => Europe/London
        )

    [c_updatedAt] => 
    [1] => ce3e4bed2954adbf05f8edfaf1a4c0cc0cea70e9
)