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;
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');
I get the error exception
[Syntax Error] line 0, col 63: Error: Expected known function, got 'YEAR'
How can I implement my query with Doctrines query builder?
Notes:
YEAR() etc. statements, e.g. as seen here. But I am looking for a way to avoid including third party plugins.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
now you can use the MONTH and YEAR statements in your DQL or querybuilder.
Note: The extension supports MySQL, Oracle, PostgreSQL and SQLite.
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句