如何在Doctrine2中使用SQL的YEAR（），MONTH（）和DAY（）？ [英] How can I use SQL's YEAR(), MONTH() and DAY() in Doctrine2?

问题描述

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

I want to perform a query which would look like this in native 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;

如果我试图在Doctrine的查询生成器中实现这一点：

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

$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');

我收到错误例外

[语法错误]行0，列63：错误：预期的已知功能，获得'YEAR'

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

可以用Doctrines查询生成器实现查询吗？

How can I implement my query with Doctrines query builder?

注意：

• 我知道主义的本机SQL 。我已经尝试过这个，但它导致我的生产和我的开发数据库表具有不同的名称的问题。我想要工作数据库不可知，所以这是没有选择的。


• 虽然我想工作db不可知：FYI，我使用MySQL。


• 有办法延长教义，以学习 YEAR（）等语句，例如如看到这里。但是我正在寻找避免包含第三方插件的方法。

• 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.
• Although I want to work db agnostic: FYI, I am using 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.

推荐答案

您可以添加原则扩展（此如果您'使用Symfony），所以你可以通过添加这个配置来使用MySql YEAR 和 MONTH 语句Symfony：

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

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

现在，您可以在DQL或查询建立器中使用MONTH和YEAR语句。

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

这篇关于如何在Doctrine2中使用SQL的YEAR（），MONTH（）和DAY（）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！