Execute date function in Symfony2 query

Question

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)

possible duplicate of [Select entries between dates in doctrine 2](http://stackoverflow.com/questions/11553183/select-entries-between-dates-in-doctrine-2) — Nicolai Fröhlich, Mar 03 '15 at 09:25

score 4 · Answer 1 · edited May 23 '17 at 12:00

4

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;

edited May 23 '17 at 12:00

Community

1
1

answered Mar 03 '15 at 09:17

Alexandru Olaru

6,842
6
27
53

can you give me the solution because i am totally new on symfony2 – Prabhash Rawat Mar 03 '15 at 09:20
i am going to try this if still i have faced any problem then i`ll let you know – Prabhash Rawat Mar 03 '15 at 09:28
where can i create this class? – Prabhash Rawat Mar 03 '15 at 09:29
I'l sugest to make a folder in your bundle `DoctrineCustomFunctions` or `DoctrineFunctions` and place it there, or you can use the Symfony 2 sugested way, create the `DQL` folder and place them there, you can then make them global by configuring them in config.yml follow the link http://symfony.com/doc/current/cookbook/doctrine/custom_dql_functions.html – Alexandru Olaru Mar 03 '15 at 09:33
is there anyother simple method for doing this.Because i am totally new on symfony2 and i have no idea about more on this – Prabhash Rawat Mar 03 '15 at 09:43
1

This is the way Doctrine 2 works, can't help you more – Alexandru Olaru Mar 03 '15 at 10:12

Execute date function in Symfony2 query

1 Answers1