MySQL 如何在 Doctrine2 中使用 SQL 的 YEAR()、MONTH() 和 DAY()？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18826836/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I use SQL's YEAR(), MONTH() and DAY() in Doctrine2?
提问by Gottlieb Notschnabel
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 列：错误：预期已知函数，得到“YEAR”
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.
- 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 的 Native SQL。我已经尝试过这个，但它导致了我的生产数据库表和我的开发数据库表具有不同名称的问题。我想工作数据库不可知，所以这是没有选择的。
- 虽然我想工作 db agnostic：仅供参考，我正在使用 MySQL。
- 有一种方法可以扩展 Doctrine 以“学习”
YEAR()等语句，例如见这里。但我正在寻找一种避免包含第三方插件的方法。
回答by alex88
You can add Doctrine extensionso you can use the MySql YEARand MONTHstatement by adding this configuration if you're on Symfony:
如果您使用的是 Symfony，您可以添加Doctrine 扩展，以便您可以通过添加此配置来使用 MySqlYEAR和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 或查询构建器中使用 MONTH 和 YEAR 语句。
Note: The extension supports MySQL, Oracle, PostgreSQL and SQLite.
注意：扩展支持 MySQL、Oracle、PostgreSQL 和 SQLite。
回答by betonowelasy
In Symfony 4you must install DoctrineExtensions:
在Symfony 4 中，你必须安装DoctrineExtensions：
composer require beberlei/DoctrineExtensions
And then edit the doctrine config file (config/packages/doctrine.yaml) as follow:
然后编辑学说配置文件（config/packages/doctrine.yaml）如下：
doctrine:
orm:
dql:
string_functions:
MONTH: DoctrineExtensions\Query\Mysql\Month
YEAR: DoctrineExtensions\Query\Mysql\Year
回答by drAlberT
orocrm/doctrine-extensionsseems to be a good project too
orocrm/doctrine-extensions似乎也是一个不错的项目
It supports both MySQLand PostgreSql.. the goal is to be cross DB
它同时支持MySQL和PostgreSql.. 目标是跨数据库
回答by Jorge P.
For Symfony 4:
对于 Symfony 4：
- Install:composer require beberlei/doctrineextensions
- Edit:config\packages\doctrine.yaml
- 安装：composer 需要 beberlei/doctrineextensions
- 编辑：config\packages\doctrine.yaml
doctrine:
orm:
dql:
datetime_functions:
DAY: DoctrineExtensions\Query\Mysql\Day
MONTH: DoctrineExtensions\Query\Mysql\Month
YEAR: DoctrineExtensions\Query\Mysql\Year
- Edit your controller:
- 编辑您的控制器：
public function somex()
{
$em = $this->getDoctrine()->getManager();
$emConfig = $em->getConfiguration();
$emConfig->addCustomDatetimeFunction('YEAR', 'DoctrineExtensions\Query\Mysql\Year');
$emConfig->addCustomDatetimeFunction('MONTH', 'DoctrineExtensions\Query\Mysql\Month');
$emConfig->addCustomDatetimeFunction('DAY', 'DoctrineExtensions\Query\Mysql\Day');
$day = '22';
$month = '4';
$qb = $em->createQueryBuilder()
->select('u')
->from('App\Entity\User', 'u')
->where('DAY(u.somedate) = :day')
->andwhere('MONTH(u.somedate) = :month')
->setParameter('month', $day)
->setParameter('day', $month)
;
$trab = $qb->getQuery()->getResult();
return $this->render('intranet/somex.html.twig', [
'trab' => $trab
]);
}
````
回答by babysure
go to this pageand choose your database systeme and pick up functions thats you want
转到此页面并选择您的数据库系统并选择您想要的功能
... I too had the same problem as you... then I modified my doctrine.yaml file using exactly the same names as in the github file and it worked.
...我也有和你一样的问题......然后我使用与 github 文件中完全相同的名称修改了我的学说.yaml文件并且它起作用了。
