如何在 Doctrine2 中使用 SQL 的 YEAR()、MONTH() 和 DAY()?

2021-11-20 00:00:00 sql mysql doctrine-orm sql-date-functions

我想在本机 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?

注意事项:

  • 我了解 Doctrine 的原生 SQL.我已经尝试过这个，但它导致了我的生产数据库表和我的开发数据库表具有不同名称的问题.我想在数据库不可知的情况下工作，所以没有选择.
  • 虽然我想工作与数据库无关:仅供参考，我使用的是 MySQL.
  • 有一种方法可以扩展 Doctrine 以学习"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.

推荐答案

你可以添加Doctrine extension 所以如果您使用的是 Symfony，则可以通过添加此配置来使用 MySql YEAR 和 MONTH 语句:

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:
    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.

注意:扩展支持 MySQL、Oracle、PostgreSQL 和 SQLite.

Note: The extension supports MySQL, Oracle, PostgreSQL and SQLite.

相关文章