Hello everyone!

We have been investing plenty of personal time and energy for many years to share our knowledge with you all. However, we now need your help to keep this blog running. All you have to do is just click one of the adverts on the site, otherwise it will sadly be taken down due to hosting etc. costs. Thank you.

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
)