CODEWITHSUNDEEP
phpmysqlsymfonydoctrine-orm
Prabhash Rawat
Prabhash Rawat

Reputation: 441

Execute date function in Symfony2 query

I want to execute date() function in Symfony2 query, below is what I've have tried but it gives me an error.

$query=$em->createQueryBuilder()
           ->select('date(a.created_at), COUNT(date( a.created_at) ) AS        count_user')
           ->from('ContrateAdminBundle:Artist', 'a')
           ->where("date(a.created_at) BETWEEN '{$fromdateCelebrities}' AND '{$todateCelebrities}'")
           ->groupBy('date(a.created_at)')
           ->getQuery();

I have also pasted the output string of my query, which I can execute without any error directly in MySQL.

select date(created_at) created_at, count(*) from Artist where date(created_at) BETWEEN '2015-01-05' AND '2015-02-25' group by date(created_at)

Upvotes: 1

Views: 258

Answers (1)

Alexandru Olaru
Alexandru Olaru

Reputation: 7092

The error is caused by doctrine 2, it does not understand the mysql method date, you should create it manualy.

I saw the sollution alredy on Create custom Doctrine 2 Method

  • Create a php class named DateFunction.php with the following code:

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

class DateFunction extends FunctionNode
{
    private $arg;

    public function getSql(SqlWalker $sqlWalker)
    {
        return sprintf('DATE(%s)', $this->arg->dispatch($sqlWalker));
    }

    public function parse(Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);

        $this->arg = $parser->ArithmeticPrimary();

        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }
}

  • In your controller or repository after you got the $em register the new method

    $em->getConfiguration()->addCustomDatetimeFunction('DATE', 'DateFunction');

This will register your custom doctrine method;

Upvotes: 4

Related Questions