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.
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) . ')';
}
}
# sport/app/config/config.yml
doctrine:
orm:
dql:
string_functions:
SHA1: Football\FrontendBundle\DQL\Sha1
# 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);
}
}
/**
* @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;
}
}
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
)