在学说Querybuilder中使用`DATE（）` [英] Using `DATE()` in Doctrine Querybuilder

问题描述

我需要获取所有行，其中 DATE（a.when）与字符串 2014-09-30 匹配

I need to get all rows where DATE(a.when) matches the string 2014-09-30.

$builder = $this->em->createQueryBuilder();
$builder->select('a')
        ->from('Entity\Appointment', 'a')
        ->andWhere('a.when = :date')
        ->setParameter('date', $date);

a。当为完整的 DATETIME ; ：date 只是一个字符串（格式为 DATE ） 。

a.when is a full DATETIME; :date is only a string (in DATE format).

以下内容和变体无效：

        ->andWhere('DATE(a.when) = :date')

Error: Expected known function, got 'DATE'

这里的正确用法是什么？

What's the correct usage here?

推荐答案

这实际上是很常见的题。
事实证明，并不是所有的sql数据库都支持DATE函数，因此负责Doctrine的好人决定不本地支持它。

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

然后将其连接到应用程序的学说部分/config.yml：

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

还有其他包含更多SQL的捆绑软件功能。奇怪的是，几年前我第一次看时，没有一个定义Date。所以我只是自己做。

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.

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

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

更新01

我没有仔细检查标签，并认为这是Symfony 2应用程序。 Date类保持不变。您可以通过获取原则配置对象进行连接。

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.

这篇关于在学说Querybuilder中使用`DATE（）`的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！