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 — Nicolai Fröhlich
– Nicolai Fröhlich, Commented Mar 3, 2015 at 9:25

Community · Accepted Answer · 2017-05-23 12:00:25Z

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, 2017 at 12:00

CommunityBot

11 silver badge

answered Mar 3, 2015 at 9:17

Alexandru Olaru

7,1426 gold badges31 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Prabhash Rawat Over a year ago

can you give me the solution because i am totally new on symfony2

Prabhash Rawat Over a year ago

i am going to try this if still i have faced any problem then i`ll let you know

Prabhash Rawat Over a year ago

where can i create this class?

Alexandru Olaru Over a year ago

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 symfony.com/doc/current/cookbook/doctrine/…

Alexandru Olaru Over a year ago

This is the way Doctrine 2 works, can't help you more

|

Collectives™ on Stack Overflow

Execute date function in Symfony2 query

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related