How to Use Date() in Doctrine 2 Dql

How can I use DATE() in Doctrine 2 DQL?

DQL is only aware of few standard sql functions (coalesce for example). To be able to use your custom function you need to register it and tell doctrine how to translate it into raw sql. Follow these guides:

Symfony Doc

Doctrine Doc

And check my answer here

How can I use now() in Doctrine 2 DQL?

The equivalent of MySQL's NOW() is Doctrine DQL's CURRENT_TIMESTAMP().

CURRENT_DATE() only returns the date part.

Reference: DQL date/time related functions

Doctrine DQL Date as parameter problem

Couldn'y make it work with the parameter so here is what I came to finally :

$now = new DateTime;
$now->modify( '-'.(date('j')-1).' day' );

$qb = DoctrineHelper::getEntityManager()->createQueryBuilder()
                        ->select("u.surname, count(u.surname) as total")
                        ->from("User", "u")
                        ->from("AbstractEvent", "e")
                        ->from("Attendance", "a")
                        ->where("u = a.attendee")
                        ->andWhere("e = a.event")
                        ->andWhere("a.status=1")
                        ->andWhere("e.date<CURRENT_TIMESTAMP()")
                        ->andWhere("e.date > '".$now->format("Y-m-d H:i:s")."'")
                        ->groupBy("u.email")
                        ->orderBy("total","desc");

Select entries between dates in doctrine 2

You can do either…

$qb->where('e.fecha BETWEEN :monday AND :sunday')
   ->setParameter('monday', $monday->format('Y-m-d'))
   ->setParameter('sunday', $sunday->format('Y-m-d'));

or…

$qb->where('e.fecha > :monday')
   ->andWhere('e.fecha < :sunday')
   ->setParameter('monday', $monday->format('Y-m-d'))
   ->setParameter('sunday', $sunday->format('Y-m-d'));

Using `DATE()` in Doctrine Querybuilder

This actually is a very common question.
It turns out that not all sql databases support a DATE function, so the good people in charge of Doctrine decided not to support it nativelly.

Kind of wish they did because it would have saved a bunch of people a fair amount of effort.

So add this rather magical class to your project:

namespace Cerad\Bundle\CoreBundle\Doctrine\DQL;

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

class Date extends FunctionNode
{
    public $date;

    public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
    {
        return "DATE(" . $sqlWalker->walkArithmeticPrimary($this->date) . ")";
    }
    public function parse(\Doctrine\ORM\Query\Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);

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

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

Then wire it up in the doctrine section of your app/config.yml:

doctrine:
  orm:
  default_entity_manager:       default
  auto_generate_proxy_classes: %kernel.debug%

  entity_managers:

    default:
      connection: default
      ...
      dql:
        datetime_functions:
          date:  Cerad\Bundle\CoreBundle\Doctrine\DQL\Date

http://doctrine-orm.readthedocs.org/en/latest/cookbook/dql-user-defined-functions.html
http://symfony.com/doc/current/cookbook/doctrine/custom_dql_functions.html
http://symfony.com/doc/current/reference/configuration/doctrine.html

There are other bundles out there with more sql functions. Oddly enough, the first time I looked a few years ago, none of them had Date defined. So I just made my own.

====================================================================

Update 01

I did not check the tags carefully and assumed that this was a Symfony 2 application. The Date class stays the same. You wire it up by getting the doctrine configuration object.

$config = new \Doctrine\ORM\Configuration();
$config->addCustomDatetimeFunction('DATE', 'blahblah\Date');

Check the Doctrine link for details.

---

Related Topics