如何在Doctrine2中使用SQL的YEAR（），MONTH（）和DAY（）？

I want to perform a query which would look like this in native SQL:

我想在本机SQL中执行一个看起来像这样的查询：

SELECT
    AVG(t.column) AS average_value
FROM
    table t
WHERE
    YEAR(t.timestamp) = 2013 AND
    MONTH(t.timestamp) = 09 AND
    DAY(t.timestamp) = 16 AND
    t.somethingelse LIKE 'somethingelse'
GROUP BY
    t.somethingelse;

If I am trying to implement this in Doctrine's query builder like this:

如果我试图在Doctrine的查询生成器中实现这个，如下所示：

$qb = $this->getDoctrine()->createQueryBuilder();
$qb->select('e.column AS average_value')
   ->from('MyBundle:MyEntity', 'e')
   ->where('YEAR(e.timestamp) = 2013')
   ->andWhere('MONTH(e.timestamp) = 09')
   ->andWhere('DAY(e.timestamp) = 16')
   ->andWhere('u.somethingelse LIKE somethingelse')
   ->groupBy('somethingelse');

I get the error exception

我收到错误异常

[Syntax Error] line 0, col 63: Error: Expected known function, got 'YEAR'

[语法错误]第0行，第63行：错误：预期的已知函数，得到'年'

How can I implement my query with Doctrines query builder?

如何使用Doctrines查询构建器实现我的查询？

Notes:

笔记：

I know about Doctrine's Native SQL. I've tried this, but it leads to the problem that my productive and my development database tables have different names. I want to work database agnostic, so this is no option.
我知道Doctrine的Native SQL。我试过这个，但它导致了我的高效和我的开发数据库表有不同名称的问题。我想工作数据库不可知，所以这是没有选择。
Although I want to work db agnostic: FYI, I am using MySQL.
虽然我想工作db不可知：FYI，我正在使用MySQL。
There is way to extend Doctrine to "learn" the YEAR() etc. statements, e.g. as seen here. But I am looking for a way to avoid including third party plugins.
有办法将Doctrine扩展到“学习”YEAR（）等语句，例如：如此处所见。但我正在寻找避免包含第三方插件的方法。

2 个解决方案

#1

You can add Doctrine extension so you can use the MySql YEAR and MONTH statement by adding this configuration if you're on Symfony:

您可以添加Doctrine扩展，以便在Symfony上添加此配置时可以使用MySql YEAR和MONTH语句：

doctrine:
    orm:
        dql:
            string_functions:
                MONTH: DoctrineExtensions\Query\Mysql\Month
                YEAR: DoctrineExtensions\Query\Mysql\Year

now you can use the MONTH and YEAR statements in your DQL or querybuilder.

现在您可以在DQL或querybuilder中使用MONTH和YEAR语句。

#2

orocrm/doctrine-extensions seems to be a good project too

orocrm / doctrine-extensions似乎也是一个很好的项目

It supports both MySQL and PostgreSql .. the goal is to be cross DB

它支持MySQL和PostgreSql ..目标是跨数据库

#1