在 Doctrine 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('EntityAppointment', 'a')
        ->andWhere('a.when = :date')
        ->setParameter('date', $date);

a.when 是完整的 DATETIME；:date 只是一个 string (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 CeradBundleCoreBundleDoctrineDQL;

use DoctrineORMQueryLexer;
use DoctrineORMQueryASTFunctionsFunctionNode;

class Date extends FunctionNode
{
    public $date;

    public function getSql(DoctrineORMQuerySqlWalker $sqlWalker)
    {
        return "DATE(" . $sqlWalker->walkArithmeticPrimary($this->date) . ")";
    }
    public function parse(DoctrineORMQueryParser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);

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

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

然后将它连接到你的 app/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:  CeradBundleCoreBundleDoctrineDQLDate

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

还有其他包含更多 sql 函数的捆绑包.奇怪的是，几年前我第一次查看时，它们都没有定义日期.所以我自己做了.

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 DoctrineORMConfiguration();
$config->addCustomDatetimeFunction('DATE', 'blahblahDate');

查看 Doctrine 链接了解详情.

Check the Doctrine link for details.

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