Utilizzando Symfony2 e Doctrine, e possibile aggiungere la funzione DQL in questo modo:
doctrine-project
Creiamo il file Acme/DemoBundle/DQL/DateDiff.php
<?php
namespace Acme\AdminBundle\DQL;
use Doctrine\ORM\Query\AST\Functions\FunctionNode;
use Doctrine\ORM\Query\Lexer;
/**
* DateDiffFunction ::= "DATEDIFF" "(" ArithmeticPrimary "," ArithmeticPrimary ")"
*/
class DateDiff extends FunctionNode
{
// (1)
public $firstDateExpression = null;
public $secondDateExpression = null;
public function parse(\Doctrine\ORM\Query\Parser $parser)
{
$parser->match(Lexer::T_IDENTIFIER); // (2)
$parser->match(Lexer::T_OPEN_PARENTHESIS); // (3)
$this->firstDateExpression = $parser->ArithmeticPrimary(); // (4)
$parser->match(Lexer::T_COMMA); // (5)
$this->secondDateExpression = $parser->ArithmeticPrimary(); // (6)
$parser->match(Lexer::T_CLOSE_PARENTHESIS); // (3)
}
public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
{
return 'DATEDIFF(' .
$this->firstDateExpression->dispatch($sqlWalker) . ', ' .
$this->secondDateExpression->dispatch($sqlWalker) .
')'; // (7)
}
}
A questo punto é necessario registrare la funzione nel file app/config/config.yml
orm:
/ ... /
dql:
datetime_functions:
datediff: Acme\DemoBundle\DQL\DateDiff
A questo punto sarà possibile scrivere query del genere:
SELECT p FROM DoctrineExtensions\Query\BlogPost p WHERE DATEDIFF(CURRENT_TIME(), p.created) < 7
... o del genere ;) ...
$repository = $this->getDoctrine()
->getRepository('AcmeDemoBundle:Ads');
$query = $repository->createQueryBuilder('a')
->where('a.idUser = :idUser')
->andWhere('DATEDIFF(a.datejob, CURRENT_DATE()) >= 1')
->setParameter('idUser', $this->getUser()->getId())
->getQuery();
$ads = $query->getResult();
