Use a DATE() function in a WHERE clause with DQL

Question

I get a strange error when I execute this DQL query:

SELECT u FROM User u LEFT JOIN u.schedule s WHERE DATE(s.timestamp) = DATE(NOW())

The exception is thrown by Doctrine with the message:

Expected known function, got 'DATE'

The problem looks similar to this bug, but that addresses the DATE() function in a GROUP BY clause and the bug is closed for Doctrine 2.2. At this moment, I get the exception with doctrine 2.4-DEV.

The query is meant to select all users scheduled for today. Is there any way I can create this DQL? I tested the SQL version in phpMyAdmin and there the query does not raise an error. What might be wrong?

Sciurine

Doctrine2 DQL doesn't have a DATE() or NOW() function: doctrine-orm.readthedocs.org/en/latest/reference/…. But do you really want to compare two timestamp on their equality? – Neptunian

I have scheduled patients for an appointment (date + time) and I want to list all patients for today. There is an ugly way to do this: prepare the statement with a php DateTime object for "today". And split the datetime field into a "date" and "time" part. I can overcome the first ugly part, but the second is just screwing with your db because an abstraction layer problem. So that's why I hoped for a solution where this would work :) I tried a SELECT CAST(s.timestamp as DATE) as schedule_day FROM ... WHERE schedule_day = :today but it returned a similar error... – Sciurine

Answer 1

You can achieve what you want by using a custom function:

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);
    }
}

Then registering this function in your code:

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

And your DQL query will work!

Adamski

WIll try, thanks! This could save me a lot of headaches where I'd have split the scheduled timestamp in a separated day and time part – Sciurine

Here is how to register the new DQL function in a Symfony2 app: symfony.com/doc/current/cookbook/doctrine/… – Desired

Also don't forget to put namespace definition in you class, otherwise symfony will complain class was not found in file. – Thickskinned

There is a very cool composer package for that which includes almost every MySQL function: github.com/beberlei/DoctrineExtensions Also see: symfony.com/doc/current/cookbook/doctrine/… to register the functions. – Ambiguous

When I try this my DATE() function returns a string not a DateTime... is there a missing configuration option to tell the ORM... "this string should be a DateTime"? – Dyson