我想在本机 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?
注意事项:
YEAR()
等语句,例如如见此处.但我正在寻找一种避免包含第三方插件的方法.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.
这篇关于如何在 Doctrine2 中使用 SQL 的 YEAR()、MONTH() 和 DAY()?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!