Symfony Custom DoctrineFunctions always returns null

Question

I'm writing a simple Custom Doctrine Function on Symfony that computes AGE given the bithdate of the entity. Here is my function:

class AgeFunction extends FunctionNode
{
    private $birthDate;

    public function parse(\Doctrine\ORM\Query\Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        $this->birthDate = $parser->ArithmeticPrimary();
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }

    public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
    {
        $bday = $this->birthDate->dispatch($sqlWalker);
        $currDate = DateFormatter::formatDate(new \DateTime());

        return "TIMESTAMPDIFF(YEAR, {$bday}, '{$currDate}')";
    }
}

And here is how i used it:

public function getAge()
{
    $qb = $this->createQueryBuilder('s')
        ->select('AGE(s.dateOfBirth)')
        ->orderBy('s.id', 'DESC');

    dump($qb->getQuery()->getResult());
}

This is the query produced:

SELECT TIMESTAMPDIFF(YEAR, s0_.date_of_birth, '2017-04-13') AS sclr_0 FROM suspect s0_ ORDER BY s0_.id DESC;

I think whats wrong here is s0_.date_of_birth never gets the actual value since when i replace it manually it works well.

So how can I do this? Thanks.

Does the produced query shows you any result when you run it directly in you sql client (like phpmyadmin) ? I tested it in one of my database and the query seems correct. — Chuck Norris
– Chuck Norris, Commented Apr 13, 2017 at 6:55
I'm a bit curious, why handling the age by doctrine and not php? — goto
– goto, Commented Apr 13, 2017 at 8:26

BentCoder · Accepted Answer · 2017-04-13 09:57:16Z

Maybe you're originally trying to do something else but the business requirement seems weird to me cos you're trying get just last person's age . Anyway let me just ignore it for now and focus on what you need. I've checked the example below and worked fine.

DQL

namespace My\Bundle\Product\APIBundle\Entity\DQL;

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

class TimestampDiff 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 sprintf(
            'TIMESTAMPDIFF(YEAR, %s, %s)',
            $this->value->dispatch($sqlWalker),
            date('Y-m-d')
        );
    }
}

REPOSITORY

public function findAge()
{
    $qb = $this->createQueryBuilder('s')
        ->select('TIMESTAMPDIFF(s.dateOfBirth) AS Age')
        ->orderBy('s.id', 'DESC')
        ->setMaxResults(1);

    return $qb->getQuery()->getResult(Query::HYDRATE_SIMPLEOBJECT);
}

CALL

$p = $this->suspectRepository->findAge();

REGISTER (My setup is different so you can check links below to make it work for your setup)

# app/config.yml

doctrine:
    dbal:
      default_connection: hello
      connections:
        hello:
          driver: "%database_driver%"
          host:   "%database_host%"
          ....
        ....
    orm:
        default_entity_manager: hello

        entity_managers:
            hello:
                dql:
                    string_functions:
                        TIMESTAMPDIFF: My\Bundle\Product\APIBundle\Entity\DQL\TimestampDiff
                connection: hello
                ....

RESULT

SELECT
TIMESTAMPDIFF(YEAR, s0_.date_of_birth, 2017-04-13) AS sclr_0
FROM suspect s0_
ORDER BY s0_.id DESC
LIMIT 1

I'll do this later and get back to you for the result :) Thanks.
and anyway, the getAge() function is just a test ill use the DoctrineFunction on other functions.

Collectives™ on Stack Overflow

Symfony Custom DoctrineFunctions always returns null

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related